[sql] SQL Server Case Statement when IS NULL

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

  case isnull(B.[stat],0)
  when 0 then dateadd(dd,10,(c.[Eventdate]))
  end

you can add in else statement if you want to add 30 days to the same .

Take a look at the ISNULL function. It helps you replace NULL values for other values. http://msdn.microsoft.com/en-us/library/ms184325.aspx

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

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

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.

Your hyphen in your ELSE statement isn't accepted in the column which is being defined under the datetime data type. You could either:

a) Wrap a CAST around your [stat] field to convert it to a varchar representation of a date

b) Use a datetime like 9999-12-31 for your ELSE value.

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.

In this situation you can use ISNULL() function instead of CASE expression

ISNULL(B.[STAT], C.[EVENT DATE]+10) AS [DATE]