SQL Server – How to Add Multiple CASE Statements

casesql server

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.

Best Answer

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

Related Solutions

SQL Server – Troubleshooting CASE Statement Returning NULL

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL.

My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.

Sql-server – Add a flag when certain conditions are met

There are any number of ways to express this in T-SQL. One that works (without using APPLY - which you should learn to love, by the way) is below, with comments inline:

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) 
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' 
    UNION ALL 
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' 
    UNION ALL  
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' 
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL 
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL 
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL 
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT
    SD.PERSON, 
    SD.TRANSACTDATE, 
    SD.STARTDATE, 
    SD.END_DATE, 
    MIN(SD.IN_PUNCH) OVER (
        PARTITION BY SD.PERSON,SD.TRANSACTDATE) AS IN_PUNCH,
    MAX(SD.OUT_PUNCH) OVER (
        PARTITION BY SD.PERSON, SD.TRANSACTDATE) AS OUT_PUNCH,
    SD.HOURS,
    SD.PAYCODE,
    -- FLAG COLUMN
    CASE
        WHEN
        (
            -- Number of LEAVE1 or LEAVE2 paycodes 
            -- for the same person and date
            SELECT COUNT_BIG(DISTINCT SD2.PAYCODE)
            FROM SampleData AS SD2
            WHERE SD2.PERSON = SD.PERSON
            AND SD2.TRANSACTDATE = SD.TRANSACTDATE
            AND SD2.PAYCODE IN ('LEAVE1', 'LEAVE2')
        )
        = 2 --Both LEAVE1 and LEAVE2 are present
        THEN
            -- Decode current paycode
            CASE SD.PAYCODE
                WHEN 'LEAVE1' THEN 1
                WHEN 'LEAVE2' THEN 2
                ELSE 999 -- Impossible
            END
        ELSE NULL
    END AS FLAG
FROM SampleData AS SD;

Output

Using APPLY

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) 
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' 
    UNION ALL 
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' 
    UNION ALL  
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' 
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL 
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL 
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL 
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT
    SD.PERSON, 
    SD.TRANSACTDATE, 
    SD.STARTDATE, 
    SD.END_DATE, 
    CA1.IN_PUNCH,
    CA2.OUT_PUNCH,
    SD.HOURS,
    SD.PAYCODE,
    -- FLAG COLUMN
    CASE
        WHEN CA3.LEAVE_PAYCODES = 2
        AND SD.PAYCODE = 'LEAVE1'
        THEN 1
        WHEN CA3.LEAVE_PAYCODES = 2
        AND SD.PAYCODE = 'LEAVE2'
        THEN 2
        ELSE NULL
    END AS FLAG
FROM SampleData AS SD
CROSS APPLY 
(
    -- Minimum IN_PUNCH for the current
    -- PERSON and TRANSACTDATE
    SELECT TOP (1) SD2.IN_PUNCH
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    ORDER BY SD2.IN_PUNCH ASC
) AS CA1
CROSS APPLY 
(
    -- Maximum OUT_PUNCH for the current
    -- PERSON and TRANSACTDATE
    SELECT TOP (1) SD2.OUT_PUNCH
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    ORDER BY SD2.OUT_PUNCH DESC
) AS CA2
CROSS APPLY 
(
    -- Count distinct LEAVEx PAYCODEs
    -- for the current PERSON and TRANSACTDATE
    SELECT COUNT_BIG(DISTINCT SD2.PAYCODE)
    FROM SampleData AS SD2
    WHERE SD2.PERSON = SD.PERSON
    AND SD2.TRANSACTDATE = SD.TRANSACTDATE
    AND SD2.PAYCODE IN ('LEAVE1', 'LEAVE2')
) AS CA3 (LEAVE_PAYCODES);