Case Statement comparing multiple dates

Question

I have read more of these questions then I care to admit in regards to this matter, however, everything I try from the answers found here still fails. So here we go...

I am trying to compare multiple dates from multiple tables to find the most recent date. The joins in the query work fine as I'm doing other comparisons and they work fine. However, whenever I try to run this case I get no syntax errors or anything it simply falls through to the ELSE even though conditions were met. This is on SQL Server 2008R2.

Code:

SELECT
[OTHER COLUMNS],
'MOST_RECENT_DATE' = Case
    WHEN A.Date > B.Date AND A.Date > C.Date AND A.Date > D.Date THEN convert(varchar, A.Date,30)
    WHEN B.Date > A.Date AND B.Date > C.Date AND B.Date > D.Date THEN convert(varchar, B.Date,30)
    WHEN C.Date > A.Date AND C.Date > B.Date AND C.Date > D.Date THEN convert(varchar, C.Date,30)
    WHEN D.Date > A.Date AND D.Date > B.Date AND D.Date > C.Date THEN convert(varchar, D.Date,30)
    ELSE 'ATTENTION'
END
FROM TABLE E
LEFT JOIN TABLE A ON E.other = A.other
LEFT JOIN TABLE B ON E.other = B.other
LEFT JOIN TABLE C ON E.other = C.other
LEFT JOIN TABLE D ON E.other = D.other

When I put in a single comparison it works and returns me the date ie.

CASE
    WHEN A.Date > B.Date THEN CONVERT(varchar,A.Date,30)
    ELSE 'WHATEVER'
END

So the issue has to lie in the multiple comparisons, I may have just been staring at this to long and need to walk away but I can't for the life of me figure out why this is falling through to the else when I know the condition is met.

Thoughts and considerations are greatly appreciated in advanced. If anyone would like more information or if I need to make myself clearer please let me know.

Solution

Consider the following table design:

create table test1 (id int, date1 date);
insert into test1 values (1, '2015-04-04'), (2, '2015-04-04');

create table test2 (id int, date2 date);
insert into test2 values (1, '2015-04-05'), (2, NULL);

This query will produce expected results for ID 1 but unexpected results for ID 2.

select *, 

case 
when test1.date1 < test2.date2 then 'test1 is smaller' 
else 'test1 is not smaller' 
end as comment

from test1
inner join test2 on test1.id = test2.id;

-- Result
id  date1       id  date2      comment
1   2015-04-04  1   2015-04-05 test1 is smaller
2   2015-04-04  2   null       test1 is not smaller

Notice that when evaluating CASE statement for id 2, control jumped to the THEN part of the CASE statement.

You could re-write your CASE statement in many ways to account for NULL values. One such way is to default the comparison to some day in the far past like so:

CASE
WHEN A.Date > COALESCE(B.Date, '1900-01-01) 
     AND A.Date > COALESCE(C.Date, '1900-01-01') 
     AND A.Date > COALESCE(D.Date, '1900-01-01') 
     THEN convert(varchar, A.Date,30)
WHEN ....
ELSE 'ATTENTION'

Comments

[bpw]

Thank you very much for the quick response. I rewrote using coalesce and everything runs as expected.

[zedfoxus]

Excellent. You should wait for new answers and then mark one of the answers as accepted to close out your question.