Select sequential rows from ROW_Number

Question

I am pulling data from a SQL Server database for a payroll system.

I have the following query:

;WITH emp AS
(
    SELECT 
        [USER_ID],
        [LOG_TIME],
        CAST([LOG_TIME] AS DATE)  AS [PunchDate],
        [DEVICE_SERIAL_NO],
        [AUTHORIZE_REASON_CODE],
        [STRINGCLOCKING_TYPE0],
        ROW_NUMBER() OVER (PARTITION BY [USER_ID], CAST([LOG_TIME] AS DATE)
                           ORDER BY [USER_ID], [LOG_TIME]) AS [RowNumber]
    FROM 
        [SekureTime].dbo.USER_TIME_LOG 
    WHERE 
        ([LOG_TIME] >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 31), 0) 
         AND [DEVICE_SERIAL_NO] = '34007965' 
         AND [AUTHORIZE_REASON_CODE] = 'Access')
        OR 
        ([LOG_TIME] >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 31), 0) 
         AND [EDIT_USER] = '4' 
         AND [CLOCKING_TYPE0] = '11') 
        OR 
        ([LOG_TIME] >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 31), 0) 
         AND [EDIT_USER] = '4'
         AND [CLOCKING_TYPE0] = '8')
)
SELECT 
    t1.[USER_ID] AS [EMPID],
    t1.[PunchDate],
    t1.[DEVICE_SERIAL_NO],
    t1.[AUTHORIZE_REASON_CODE],
    t1.[STRINGCLOCKING_TYPE0],
    CONVERT(TIME(0), MIN(t1.[LOG_TIME]))  AS [punch1],
    CONVERT(TIME(0), MAX(t2.[LOG_TIME])) AS [punch2],
    SUM(ISNULL(DATEDIFF(MI, t1.[LOG_TIME], t2.[LOG_TIME]), 0)) AS [TotalMinutes]
FROM  
    emp AS t1
LEFT JOIN 
    emp AS t2 ON (t1.[USER_ID] = t2.[USER_ID]
                  AND t1.[PunchDate] = t2.[PunchDate]
                  AND t1.[RowNumber] = (t2.[RowNumber] - 1)
                  AND t2.[RowNumber] % 2 = 0)
GROUP BY 
    t1.[USER_ID],
    t1.[PunchDate],
    t1.[DEVICE_SERIAL_NO],
    t1.[AUTHORIZE_REASON_CODE],
    t1.[STRINGCLOCKING_TYPE0]

It works fine when I have employees punching only 2 times during a day but some of them are actually punching 4 times (punch out for lunch and punch in from lunch).

How can I get the 4 punches from those days ? Instead of only the first one (MIN) and last one (MAX) ?

The expected result should be something like:

Punch Date  Emp Number  PunchDetail In      Out
9/5/2020    30919       47590       10:00 AM    7:30 PM
9/6/2020    32246       47591       10:45 AM    7:00 PM
9/6/2020    34015       47592       10:30 AM    7:00 PM
9/6/2020    34334       47593       10:15 AM    2:15 PM  <--
9/6/2020    34334       47594        2:55 AM    7:15 PM  <--
9/7/2020    34350       47595       10:30 AM    7:00 PM
9/7/2020    34792       47596       10:30 AM    7:15 PM

Where the <-- show employees with 2 pairs of punches on the same day.

Answer 1

As a naive starter for ten, making these assumptions:

• The first log is Never a log-out
• Punches are always shown as pairs
• If someone logs in but not out, ignore the log

Instead of joining, just aggregate and use case statements...

;WITH emp AS
(
    SELECT 
        [USER_ID],
        [LOG_TIME],
        CAST([LOG_TIME] AS DATE)  AS [PunchDate],
        [DEVICE_SERIAL_NO],
        [AUTHORIZE_REASON_CODE],
        [STRINGCLOCKING_TYPE0],
        ROW_NUMBER() OVER (PARTITION BY [USER_ID], CAST([LOG_TIME] AS DATE)
                           ORDER BY [USER_ID], [LOG_TIME]) AS [RowNumber]
    FROM 
        [SekureTime].dbo.USER_TIME_LOG 
    WHERE 
        ([LOG_TIME] >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 31), 0) 
         AND [DEVICE_SERIAL_NO] = '34007965' 
         AND [AUTHORIZE_REASON_CODE] = 'Access')
        OR 
        ([LOG_TIME] >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 31), 0) 
         AND [EDIT_USER] = '4' 
         AND [CLOCKING_TYPE0] = '11') 
        OR 
        ([LOG_TIME] >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 31), 0) 
         AND [EDIT_USER] = '4'
         AND [CLOCKING_TYPE0] = '8')
),
    pivotted AS
(
    SELECT 
        emp.[USER_ID] AS [EMPID],
        emp.[PunchDate],
        emp.[DEVICE_SERIAL_NO],
        emp.[AUTHORIZE_REASON_CODE],
        emp.[STRINGCLOCKING_TYPE0],
        CONVERT(TIME(0), MAX(CASE WHEN emp.[RowNumber] % 2 = 1 THEN emp.[LOG_TIME] END))  AS [punch1],
        CONVERT(TIME(0), MAX(CASE WHEN emp.[RowNumber] % 2 = 0 THEN emp.[LOG_TIME] END))  AS [punch2]
    FROM  
        emp
    GROUP BY 
        emp.[USER_ID],
        emp.[PunchDate],
        emp.[DEVICE_SERIAL_NO],
        emp.[AUTHORIZE_REASON_CODE],
        emp.[STRINGCLOCKING_TYPE0],
        (emp.[RowNumber]-1) / 2
)
SELECT
    *,
    ISNULL(DATEDIFF(MI, [punch1], [punch2]), 0)   AS [TotalMinutes]
FROM
    pivotted

GROUP BY (emp.[RowNumber]-1) / 2 ensures everything is grouped in to sequential pairs, due to integer arithmetic.

1  =>  (1-1)/2  =>  0/2  =>  0
2  =>  (2-1)/2  =>  1/2  =>  0
3  =>  (3-1)/2  =>  2/2  =>  1
4  =>  (4-1)/2  =>  3/2  =>  1
5  =>  (5-1)/2  =>  4/2  =>  2