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 

8 Answers

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.

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.

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

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

 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 .

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

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

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.

Take a look at the ISNULL function. It helps you replace NULL values for other values.

Slight variation on Mahesh solution for mssql:

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