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.
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 |