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

sql serversql-server-2008

I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.

Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.

Thank you and really appreciate all the help I get from this wonderful group.

Sample Table

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 Person
    , TransactDate, STARTDATE, END_DATE
    , MIN(IN_PUNCH) OVER (PARTITION BY Person,TransactDate)  AS IN_PUNCH
    , MAX(OUT_PUNCH) OVER (PARTITION BY Person, TransactDate) AS OUT_PUNCH
    ,HOURS
    ,PAYCODE
FROM SampleData

Current Results

Person  TransactDate STARTDATE  END_DATE    IN_PUNCH OUT_PUNCH HOURS PAYCODE
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     3     REG1
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     4     REG2
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     4     LEAVE1
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     2     LEAVE2
1234    08/05/2015   08/05/2015 08/05/2015  08:00    09:00     1     LEAVE1
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     2     LEAVE2
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     1     REG1

Desired Results

Person  TransactDate STARTDATE  END_DATE    IN_PUNCH OUT_PUNCH HOURS PAYCODE FLAG
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     3     REG1    NULL
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     4     REG2    NULL
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     4     LEAVE1  1
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     2     LEAVE2  2
1234    08/05/2015   08/05/2015 08/05/2015  08:00    09:00     1     LEAVE1  NULL 
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     2     LEAVE2  NULL
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     1     REG1    NULL

Thank you for the quick help. Unfortunately. it's a 2008 server and I'm not familiar with OUTER APPLY. Can a solution be posted without the OUTER APPLY?

Best Answer

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

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);
Related Question