Comparing DateTime Values

SQL 2008

I have three datetime fields I am comparing in a CASE statement below. The problem I am having is with NULL values. If one field has a datetime value in it and the other 2 have nulls then the result is NULL. How can I avoid this. Thanks

    CASE WHEN dy.LastCall > emaildate
                 AND dy.LastCall > crystalcldate
                 THEN dy.LastCall
            WHEN emaildate > crystalcldate
                 AND emaildate > dy.LastCall
                 THEN emaildate
            WHEN crystalcldate > dy.LastCall
                 AND crystalcldate > z.emaildate
                 THEN cm.crystalcldate END crystalcldate
Mark WilsonBI DeveloperAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Let SQL do the sorting; this will also be much, much easier if/when you have more than 3 dates to check:

SELECT ca1.crystalcldate
FROM ...
CROSS APPLY (
    SELECT TOP (1) dates.crystalcldate
    FROM (
        VALUES(dy.LastCall),(emaildate),(cm.crystalcldate) /*,(date4),(date5),...*/
    ) AS dates(crystalcldate)
    ORDER BY dates.crystalcldate DESC
) AS ca1
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Let us know what you want to do when a NULL is found.
0
 
Mark WilsonBI DeveloperAuthor Commented:
Hi

If for instance all dates are null then null is returned

If LastCall has a datetime in it and emaildate and crystalcldate are null then it will show LastCall datetime
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nitin SontakkeDeveloperCommented:
DISCLAIMER FIRST: This is workable solution, only if, you are not talking about millions of rows in a table. Personally, I haven't obviously tested, however, SQL Server UDFs are known to be performance degraders.

Here is a proposed solution. Not quite a great one and personally, I am not satisfied with the code in the function. However, this looks concise.

set ansi_nulls on
go

set quoted_identifier on
go

create function [dbo].[greatest] (@date1 datetime, @date2 datetime)
returns datetime
begin
   if (@date1 is null and @date2 is null) return null;
   if (@date1 is null and @date2 is not null) return @date2;
   if (@date1 is not null and @date2 is null) return @date1;
   return case when @date1 <= @date2 then @date2 else @date1 end;
end;
go

-- Unit testing of the function...

declare @date01 date = null, @date02 date = null, @date03 date = null;                    -- 0 0 0 = 0
select [dbo].[greatest](@date01, [dbo].[greatest](@date02, @date03)) -- null

declare @date04 date = null, @date05 date = null, @date06 date = '20170901';              -- 0 0 1 = 1
select [dbo].[greatest](@date04, [dbo].[greatest](@date05, @date06)) -- 2017-09-01 00:00:00.000

declare @date07 date = null, @date08 date = '20170501', @date09 date = null;              -- 0 1 0 = 2
select [dbo].[greatest](@date07, [dbo].[greatest](@date08, @date09)) -- 2017-05-01 00:00:00.000

declare @date10 date = null, @date11 date = '20170501', @date12 date = '20170901';        -- 0 1 1 = 3
select [dbo].[greatest](@date10, [dbo].[greatest](@date11, @date12)) -- 2017-09-01 00:00:00.000

declare @date13 date = '20170101', @date14 date = null, @date15 date = null;              -- 1 0 0 = 4
select [dbo].[greatest](@date13, [dbo].[greatest](@date14, @date15)) -- 2017-01-01 00:00:00.000

declare @date16 date = '20170101', @date17 date = null, @date18 date = '20170901';        -- 1 0 1 = 5
select [dbo].[greatest](@date16, [dbo].[greatest](@date17, @date18)) -- 2017-09-01 00:00:00.000

declare @date19 date = '20170101', @date20 date = '20170501', @date21 date = null;        -- 1 1 0 = 6
select [dbo].[greatest](@date19, [dbo].[greatest](@date20, @date21)) -- 2017-05-01 00:00:00.000

declare @date22 date = '20170101', @date23 date = '20170501', @date24 date = '20170901';  -- 1 1 1 = 7
select [dbo].[greatest](@date22, [dbo].[greatest](@date23, @date24)) -- 2017-09-01 00:00:00.000

Open in new window


The code copied from https://www.sqlservercentral.com/Forums/489858/GREATEST-and-LEAST-function and modified.
0
 
HuaMin ChenSystem AnalystCommented:
Do like

CASE WHEN isnull(dy.LastCall,convert(datetime,'20180601',112)) > isnull(emaildate,convert(datetime,'19600601',112))
...

Open in new window

to each date column to avoid the NULL case!
0
 
Nitin SontakkeDeveloperCommented:
If I steal HuaMinChen's idea the complete statement would look like something as follows:

 nullif(CASE WHEN isnull(dy.LastCall, '19000101') > isnull(emaildate, '19000101')
                 AND isnull(dy.LastCall, '19000101' > isnull(crystalcldate, '19000101')
                 THEN dy.LastCall
            WHEN isnull(emaildate, '19000101') > isnull(crystalcldate, '19000101')
                 AND isnull(emaildate, '19000101') > isnull(dy.LastCall, '19000101')
                 THEN emaildate
            WHEN isnull(crystalcldate, '19000101') > isnull(dy.LastCall, '19000101')
                 AND isnull(crystalcldate, '19000101') > isnull(z.emaildate, '19000101')
                 THEN cm.crystalcldate END, '19000101') crystalcldate

Open in new window


Obviously, syntax is not checked...modify as appropriate.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
CASE 
	WHEN dy.LastCall IS NULL AND emaildate IS NULL AND crystalcldate IS NULL THEN NULL
	WHEN dy.LastCall > ISNULL(emaildate,dy.LastCall) AND dy.LastCall > ISNULL(crystalcldate, dy.LastCall) THEN dy.LastCall
	WHEN emaildate > ISNULL(crystalcldate, emaildate) AND emaildate > ISNULL(dy.LastCall, emaildate) THEN emaildate
	WHEN crystalcldate > ISNULL(dy.LastCall, crystalcldate) AND crystalcldate > ISNULL(z.emaildate, crystalcldate) THEN cm.crystalcldate 
END

Open in new window

0
 
Mark WilsonBI DeveloperAuthor Commented:
Thanks worked well
0
 
Nitin SontakkeDeveloperCommented:
@Scott Pletcher - Yes, lot better from performance point of view.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.