Click here to Skip to main content
15,892,537 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to get the null value output using case statement.
My sql query as follows

SQL
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.

so i want null value data also get processdate in biometric entry.

please help me what is the mistake i made in my above sql query.
Posted
Updated 11-Nov-14 22:16pm
v3
Comments
Maciej Los 12-Nov-14 3:21am    
Please, be more specific and shortly describe your issue. "I want to get the null value output using case statement" is not enough.
Shweta N Mishra 12-Nov-14 3:22am    
What do you get, What values your table contains ?
Thava Rajan 12-Nov-14 3:24am    
few questions
when did you expect a null?
what is the purpose of where condition there it almost filter all the things?
[no name] 12-Nov-14 4:19am    
e.g.
CAST NULL AS INTEGER
CAST NULL AS CHAR(20)

1 solution

SQL
CASE
  WHEN firsthalf IS NULL AND secondhalf IS NULL then NULL
END

Of course you can use any other logical operator than AND - it depends on your needs.
 
Share this answer
 
Comments
Maciej Los 12-Nov-14 4:32am    
+5!
Kornfeld Eliyahu Peter 12-Nov-14 4:38am    
Thank you...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900