SQL Server Case Statement when IS NULL

Question

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

Answer 1

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.

Answer 2

I agree with Joachim that you should replace the hyphen with NULL. But, if you really do want a hyphen, convert the date to a string:

(CASE WHEN B.[STAT] IS NULL       THEN convert(varchar(10), C.[EVENT DATE]+10, 121)       ELSE '-'  END) AS [DATE] 

Also, the distinct is unnecessary in your select statement. The group by already does this for you.

Answer 3

You can use IIF (I think from SQL Server 2012)

SELECT IIF(B.[STAT] IS NULL, C.[EVENT DATE]+10, '-') AS [DATE]