how do get Null value using case Statemet in sqlserver

Nirmal KumarC

11y1.3k

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes11y ago
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, 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'
else null 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'
Joginder Banger11y ago
another example

declare @tmep varchar(max)='44'
begin
select case when @tmep='Joginder' then 'singh' else null end
end
Joginder Banger11y ago
select UserID, ProcessDate,Leave=(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(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'