how do get Null value using case Statemet in sqlserver

Question

I want to get the null value output using case statement.

My sql query as follows

select UserID, ProcessDate,Leave=(case when firsthalf = 'PR' and secondhalf = 'PR' then '0'

when firsthalf = 'WO' and secondhalf = 'WO' then '0'

when firsthalf = 'AB' and secondhalf = 'AB' then '1'

when firsthalf is null and secondhalf is null then '1'

when firsthalf = 'PR' and secondhalf = 'AB' then '0.5'

when firsthalf = 'AB' and secondhalf = 'PR' then '0.5'

when firsthalf = 'IN' and secondhalf is null then '0.5'

when firsthalf = 'IN' and secondhalf = 'AB' then '0.5'

when firsthalf = 'IN' and secondhalf = 'Out' then '0.5'

when firsthalf = 'Out' and secondhalf = 'IN' then '0.5' end),datename(month,processdate) as Months,datepart(yyyy,processdate) as Years

from Accounts_DailyAttendance where UserID=1003 and datename(month,processdate) = 'March' and datepart(yyyy,processdate)= '2014' and firsthalf <> 'PR' and secondhalf <> 'PR' and firsthalf <> 'WO' and secondhalf <> 'WO'

When i run the above sql query null value output is not came.

please help me what is the mistake i made in my above sql query.

Vulpes · Answer

If you mean that you want to return NULL if none of the CASE expressions are true then it should automatically do so. See notes on ELSE under the Arguments section of this link: http://msdn.microsoft.com/en-us/library/ms181765.aspx However you could make it explicit by adding an ELSE clause as follows: select UserID ProcessDateLeave=(case when firsthalf = 'PR' and secondhalf = 'PR' then '0' when firsthalf = 'WO' and secondhalf = 'WO' then '0' when firsthalf = 'AB' and secondhalf = 'AB' then '1' when firsthalf is null and secondhalf is null then '1' when firsthalf = 'PR' and secondhalf = 'AB' then '0.5' when firsthalf = 'AB' and secondhalf = 'PR' then '0.5' when firsthalf = 'IN' and secondhalf is null then '0.5' when firsthalf = 'IN' and secondhalf = 'AB' then '0.5' when firsthalf = 'IN' and secondhalf = 'Out' then '0.5' when firsthalf = 'Out' and secondhalf = 'IN' then '0.5' else null end)datename(monthprocessdate) as Monthsdatepart(yyyyprocessdate) as Years from Accounts_DailyAttendance where UserID=1003 and datename(monthprocessdate) = 'March' and datepart(yyyyprocessdate)= '2014' and firsthalf 'PR' and secondhalf 'PR' and firsthalf 'WO' and secondhalf 'WO'

Joginder Banger · Answer

another example declare @tmep varchar(max)='44' begin select case when @tmep='Joginder' then 'singh' else null end end

Joginder Banger · Answer

select UserID ProcessDateLeave=(case when firsthalf = 'PR' and secondhalf = 'PR' then '0' else null when firsthalf = 'WO' and secondhalf = 'WO' then '0' else null when firsthalf = 'AB' and secondhalf = 'AB' then '1' else null when firsthalf is null and secondhalf is null then '1' else null when firsthalf = 'PR' and secondhalf = 'AB' then '0.5' else null when firsthalf = 'AB' and secondhalf = 'PR' then '0.5' else null when firsthalf = 'IN' and secondhalf is null then '0.5' else null when firsthalf = 'IN' and secondhalf = 'AB' then '0.5' else null when firsthalf = 'IN' and secondhalf = 'Out' then '0.5' else null when firsthalf = 'Out' and secondhalf = 'IN' then '0.5' else null end)datename(monthprocessdate) as Monthsdatepart(yyyyprocessdate) as Years from Accounts_DailyAttendance where UserID=1003 and datename(monthprocessdate) = 'March' and datepart(yyyyprocessdate)= '2014' and firsthalf 'PR' and secondhalf 'PR' and firsthalf 'WO' and secondhalf 'WO'