sqlsql-serverpivotsql-server-2016partition-by

Pivot two DateTime rows into a pair of columns when one part of the pair may be missing


I am working on a time clock application for my company that is capturing clock-in/clock-out data in a table named TCPunch (TimeClock Punch). ActionID 1 = ClockIn, ActionID 2= ClockOut. If either row for a ClockIn/ClockOut pair is missing, I would like it to be null. The payroll operator will correct all missing time punches and then re-run the report.

Below is data from the TCPunch table with an ORDER BY EmployeeID, ActionTime:

ID EmployeeID ActionID ActionTime
41 17 1 2023-04-13 07:16:26.250
11 69 1 2023-04-11 14:47:36.523
14 69 2 2023-04-11 16:47:57.220
16 69 1 2023-04-11 16:48:06.683
18 69 2 2023-04-11 17:00:00.300
36 119 2 2023-04-12 10:30:18.430

How can I write an efficient SQL statement to present the data ordered by EmployeeID, ActionTime and pivot the ActionID and ActionTime, and calculate the time diff in hours to get output like below? I have studied CTE and Partition Over for this I am having no luck and this is above my current SQL knowledge. Any help would be greatly appreciated.

EmployeeID ClockIn ClockOut Hours
17 2023-04-13 07:16:26.250 NULL NULL
69 2023-04-11 14:47:36.523 2023-04-11 16:47:57.220 2.005833
69 2023-04-11 16:48:06.683 2023-04-11 17:00:00.300 0.198333
119 NULL 2023-04-12 10:30:18.430 NULL

I figured out I can calculate hours as decimal using DateDiff(second, ClockIn, ClockOut) / 3600.0 once I can get the data in the right format.


Solution

  • We group every clock in and out that belong together by employee and then use pivot.

    select   EmployeeID
            ,[1]                             as ClockIn
            ,[2]                             as ClockOut
            ,datediff(second,[1],[2])/3600.0 as hours
    from 
    (
    select   EmployeeID 
            ,ActionID   
            ,ActionTime
            ,count(case ActionID when 1 then 1 end) over(partition by EmployeeID order by ID) as flg
    from     t
    ) t
    pivot    (max(ActionTime) for ActionID in([1], [2])) t
    order by EmployeeID
    
    EmployeeID ClockIn ClockOut hours
    17 2023-04-13 07:16:26.000 null null
    69 2023-04-11 14:47:36.000 2023-04-11 16:47:57.000 2.005833
    69 2023-04-11 16:48:06.000 2023-04-11 17:00:00.000 0.198333
    119 null 2023-04-12 10:30:18.000 null

    Fiddle