Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jimtimber
Yak Posting Veteran
60 Posts |
 Posted - 2014-10-21 : 08:11:52
|
Hi,
I'm wanting to reconstruct this:
SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
ELSE Date1
END AS MostRecentDate
but if one of the date fields is null, then it doesn't work. Any ideas how I can get this to work please with nulls? My code is:
SELECT dbo.Tbl_FamiliesProgress.FamiliesID,
CASE
WHEN dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'ReApproved')
>= dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'Approved') AND dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'ReApproved')
>= dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'Proposed termination of approval by ADM')
THEN dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'ReApproved')
WHEN dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID,
'Approved') >= dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'reApproved') AND dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID,
'Approved') >= dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'Proposed termination of approval by ADM')
THEN dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'Approved')
WHEN dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID,
'Proposed termination of approval by ADM') >= dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'reApproved') AND
dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'Proposed termination of approval by ADM')
>= dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'approved') THEN dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID,
'Proposed termination of approval by ADM')
ELSE dbo.spGetMaxFamilyProgressDate(dbo.Tbl_Families.FamiliesID, 'ReApproved') END AS [RecentDate]
FROM dbo.Tbl_FamiliesProgress INNER JOIN
dbo.Tbl_Families ON dbo.Tbl_FamiliesProgress.FamiliesID = dbo.Tbl_Families.FamiliesID
Thank you for any help!
Jim
Jim |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-21 : 08:47:16
|
| First off, what do you want to do when you hit nulls? Should they satisfy 'WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1' or not? |
 |
|
|
jimtimber
Yak Posting Veteran
60 Posts |
Posted - 2014-10-21 : 08:53:06
|
Hi Gbritton, I just want to know the most recent date of 1,2 or 3. If there is a null in either of the 3 date fields then I still want to know the most recent date.
Hope that makes sense. I've tried Coalesce but it didn't like that.
Thanks
Jim |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-21 : 09:33:23
|
"If there is a null in either of the 3 date fields then I still want to know the most recent date."
What if all three date fields are null?
Instead of a CASE, you could do this:
select (SELECT MAX(dt) as maxdate from
(values (date1), (date2), (date3)) v(dt)
) as maxdate
Note that MAX will ignore null values. |
|
|
|
ElenaSTL
Starting Member
10 Posts |
Posted - 2014-10-21 : 11:13:22
|
Yes, this is interesting. I have never used values in SELECT statement. And what is v(dt) doing here? Without it code doesn't work.
declare @date1 date
set @date1 = '01/01/2014'
declare @date2 date
set @date2 = '01/02/2014'
declare @date3 date
set @date3 = null
select (SELECT MAX(dt) as maxdate from
(values (@date1), (@date2), (@date3)) v(dt)
) as maxdate |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-21 : 11:45:49
|
| the values clause needs an alias when used like this. It's basically a subquey |
|
|
|
|
|
|
|
|
how to handle null values with >=