how to handle null values with >=

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to handle null values 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources