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

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.

CodePudding user response：

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

CodePudding user response：

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)