2

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?

2

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);
| improve this answer | |

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.