Nirmal KumarC

Nirmal KumarC

  • 1.4k
  • 327
  • 78.8k

how do get Null value using case Statemet in sqlserver

Nov 12 2014 3:19 AM

  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.




Answers (3)