Case statement to ISNULL

Question

I am very new to T-SQL and am looking to simplify this query using isnull.

case 
when datediff(d, appdate, disdate) IS NOT NULL THEN datediff(d, appdate, disdate) 
ELSE 
    Case 
        when appdate is null THEN datediff(d,update,getdate())
        when disdate IS NULL THEN datediff(d,appdate,getdate())
    END
END

Possible duplicate: https://stackoverflow.com/questions/3237646/t-sql-case-clause-how-to-specify-when-null — Oddmar Dam, Jun 13 '19 at 16:12
Tip: Good information about `COALESCE` and `ISNULL` is at MSDN Blogs [here](https://web.archive.org/web/20100715082018/http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx). — HABO, Jun 13 '19 at 17:20

score 1 · Accepted Answer · answered Jun 13 '19 at 15:45

1

Not much of a simplification but this should do the same thing:

ISNULL(datediff(d, appdate, disdate) , 
    CASE WHEN appdate IS NULL THEN datediff(d,update,getdate())
         WHEN disdate IS NULL THEN datediff(d,appdate,getdate()) END
)

answered Jun 13 '19 at 15:45

Zohar Peled

79,642
10
69
121

The second argument to `ISNULL` could maybe be simplified a bit (to `datediff(DAY,IIF(appdate IS NULL,[update],appdate),getdate())`) – Martin Smith Jun 13 '19 at 15:53
@MartinSmith I thought about it but since this is a `case when...when...` and not a `case when else` I figured it's not exactly equivalent and therefor I shouldn't use `IIF` in this case. – Zohar Peled Jun 13 '19 at 15:58
@ZoharPeled Thank you very much for the idea. I used it and was able to narrow it down to: isnull(datediff(d,appdate,disdate), isnull(datediff(d,appdate,getdate()), datediff(d,update,getdate()))) – Bina Jun 13 '19 at 17:42
@MartinSmith I did not think about using IIF for this. This makes total sense. Thank you. – Bina Jun 13 '19 at 17:46

Case statement to ISNULL

1 Answers1