1

I have 2 separate case statements which are producing the correct results:

Case when A_staff_Service_Type.type = 'v' 
  then isnull(Indirect_Duration,0)/60.00 else null end

The above produces 6.5 for 1 employee.

Case when pt_service.service_code = 'N06 ' 
  then isnull(direct_Duration,0)/60.00 else null End

The above produces 5.5 for the same employee.

So I have two separate outputs of 6.5 and 5.5. What I would like is the output to be 12. I have tried

Case when A_staff_Service_Type.type = 'v' then isnull(Indirect_Duration,0)/60.00 else null end +

Case when pt_service.service_code = 'N06 ' then isnull(direct_Duration,0)/60.00 else null End

But the above results in 0 instead of 12.

This is a query writer within a clinical application based on SQL Server 2008.

  • Could you specify your database platform, some sample data and expected output? Oh and welcome to the site, Please do the tour read through the help section to learn how this site works. especially how to ask a good question. You will attract more answers and useful feedback. – Tom V Oct 29 '15 at 14:01
  • 1
    If expression A yields 6.5 and expression B yields 5.5, then the expression A+B will yield 12.0. No way it can show 0. – ypercubeᵀᴹ Oct 29 '15 at 14:24
1

Not sure what you mean, but I think you are looking for something like this:

Case 
  when A_staff_Service_Type.type = 'v' and pt_service.service_code = 'N06' 
     then isnull(Indirect_Duration,0)/60.00  + isnull(direct_Duration,0)/60.00 
  else null 
end

I also don't know if the AND is correct for your use-case. Maybe you want an OR - but based on the little information you have provided this is impossible to answer.

The obvious answer to "I want the results added" is:

Case when A_staff_Service_Type.type = 'v' 
  then isnull(Indirect_Duration,0)/60.00 else 0 end 
+
Case when pt_service.service_code = 'N06 ' 
  then isnull(direct_Duration,0)/60.00 else 0 End
  • 1
    and ifadding them, the else null should become else 0 – ypercubeᵀᴹ Oct 29 '15 at 14:20
  • Thank you! That did the trick. I changed the else null to else 0 with the + between both statements and it works. – BlueLiner Oct 29 '15 at 14:22
  • @BlueLiner: hmm, the result of adding null to something else should result in null not 0 - but maybe this is a non-standard behaviour of SQL Server. – a_horse_with_no_name Oct 29 '15 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.