Adding 2 Case statements

Question

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.

Answer 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