SQL Server Case Statement when IS NULL

sql, sql-server, sql-server-2008

Solution

CASE WHEN B.[STAT] IS NULL THEN (C.[EVENT DATE]+10)   -- Type DATETIME
     ELSE '-'                                         -- Type VARCHAR
     END AS [DATE]

You need to select one type or the other for the field, the field type can't vary by row. The simplest is to remove the `ELSE '-'` and let it implicitly get the value `NULL` instead for the second case.

Problem

I'm trying to do an IF statement type function in SQL server. Where there is a NULL in the field, I want it to take a field from one of the tables and add 10 days to it. And if possible create another column and add the 30 days. ``` SELECT DISTINCT B.[ID], MAX(A.[START DATE]), B.[STAT], C.[POST DATE], CASE WHEN (C.[POST DATE] BETWEEN C.[EVENT DATE]+10 AND C.[EVENT DATE]+30) THEN 'GOOD' END AS [BETTER VISIT], CASE WHEN B.[STAT] IS NULL THEN (C.[EVENT DATE]+10) ELSE '-' END AS [DATE] FROM #TEMP1 A FULL OUTER JOIN #TEMP2 B ON A.[ID]=B.[ID] FULL OUTER JOIN #TEMP3 C ON A.[ID]=C.[ID] GROUP BY B.[ID], B.[STAT], C.[POST DATE], C.[EVENT DATE] ORDER BY A.[START DATE] DESC ``` The result would look sort of like: ``` ID START DATE STAT POST DATE BETTER VISIT DATE DATE2 --------------------------------------------------------------------------- 1 2013-01-01 GOOD 2013-11-01 GOOD - - 2 2013-03-01 NULL NULL NULL 2013-03-11 2013-03-31 ```

Original source