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

Question

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.

group a clock-out with an immediate previous clock-in, the rest stay on their own, then aggregate per group: `select EmployeeID, min(case when ActionID=1 then ActionTime end) as ClockIn, max(case when ActionID=2 then ActionTime end) as ClockOut from (select *, sum(add1) over(partition by EmployeeID order by ActionTime rows unbounded preceding) as grp from (select *, case when ActionID=2 and lag(ActionID) over(partition by EmployeeID order by ActionTime) = 1 then 0 else 1 end as add1 from TCPunch) as t) as g group by EmployeeID, grp` — lptr, Apr 14 '23 at 02:24

DannySlor · Accepted Answer · 2023-04-14T02:54:21.110

1

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

edited Apr 14 '23 at 02:54

answered Apr 14 '23 at 02:48

DannySlor

4,574
1
3
11

This is working pretty well. The only issue I see is if the user is manually editing the data and creates a ClockIn action that has an ID value > than the corresponding ClockOut ID (Action 1 below Action 2 in ID order). It's easy to visually see the issue in the data and they can fix by deleting the pair, and re-entering in the correct ClockIn, ClockOut order. – David Christian Apr 15 '23 at 12:07
@DavidChristian .. there is a chance that some Action2 rows might “disappear” eg: for an employee there are consecutive rows Action1, Action2, Action2, Action2. All 3 action2 rows have the same count(previous Action1) and they are aggregated, out of the 3 only 1 will appear as max(ActionTime) the other two are lost. – lptr Apr 15 '23 at 15:14

score 1 · Answer 2 · answered Apr 14 '23 at 03:56

Answered by @lptr in a comment:

Group a clock-out with an immediate previous clock-in, the rest stay on their own, then aggregate per group:

select
  EmployeeID,
  min(case when ActionID = 1 then ActionTime end) as ClockIn,
  max(case when ActionID = 2 then ActionTime end) as ClockOut
from (
    select *,
      sum(add1) over (partition by EmployeeID
                order by ActionTime rows unbounded preceding) as grp
    from (
        select *,
          case when ActionID = 2
            and lag(ActionID) over (partition by EmployeeID order by ActionTime) = 1
            then 0
            else 1 end as add1
        from TCPunch
    ) as t
) as g
group by
  EmployeeID,
  grp;

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

2 Answers2