Nested case statement - what's is wrong with it?

Question

I want to write a nested case statement where i want in Microsoft SQL Server that

case 
    when date 2 is null 
        then date 1 
        else date 2 
end ---1st condition

and use this condition as:

case 
    when condition1 is <= getdate() 
         and condition1 < getdate() + 14 
        then 'DUE' 
        else 'after .

I am getting error message and not able to complete my case statement.

Your "1st condition" can just be expressed as `ISNULL(Date2, Date1)` - then use that in your `CASE` — Martin Smith, Jan 24 '22 at 11:46
*" am getting error message"* And what is that error? Also that is a `CASE` **expression**, not a `Case` statement. — Thom A, Jan 24 '22 at 11:57
`and condition1 < getdate() + 14` is irrelevent, as it will always be true if the first line is true — CompanyDroneFromSector7G, Jan 24 '22 at 12:54

score 0 · Answer 1 · answered Jan 24 '22 at 11:57

0

you don't need the 1st check because both the checks are comparing getdate

case when isnull([date 1],[date 2]) <= getdate() + 14 then 'DUE' else <'' end

answered Jan 24 '22 at 11:57

Moulitharan M

729
4
15

score 0 · Answer 2 · answered Jan 24 '22 at 12:49

I would use CROSS APPLY for this :

SELECT t2.y
FROM sometable
CROSS APPLY (SELECT CASE WHEN date 2 IS NULL THEN date 1 ELSE date 2 END) t1(x)
CROSS APPLY (SELECT CASE WHEN t1.x <= getdate() AND t1.x < getdate() + 14 THEN 'DUE' ELSE 'AFTER' END) t2(y)

Note that you can use COALESCE to make the first CROSS APPLY expression simpler, and IIF for the second :

SELECT t2.y
FROM sometable
CROSS APPLY (SELECT COALESCE(date 2, date 1)) t1(x)
CROSS APPLY (SELECT IIF(t1.x <= getdate() AND t1.x < getdate() + 14, 'DUE', 'AFTER')) t2(y)

Nested case statement - what's is wrong with it?

2 Answers2