I am trying to calculate consecutive hours based on bottom conditions.
If an employee works continuously with less than 1.5 hours (90 minutes) of interval between each punch in and punch out, those punch hours are added as consecutive hours.
However, if there is more than 90 minute interval between each punch in and out, those punch hours are not added up.
I have bottom illustration in screenshot:
Here is dataset:
select *
into #temp
from
(values
(1, 100001, '2021-12-12 23:31', '2021-12-12 23:59', '2021-12-13 00:00', 1, 0.47, 'solo/add'),
(2, 100001, '2021-12-13 00:00', '2021-12-13 03:07', '2021-12-13 03:37', 30, 3.12, 'solo/add'),
(3, 100001, '2021-12-13 03:37', '2021-12-13 07:07', '2021-12-13 23:17', 970, 3.5, 'no add'),
(4, 100001, '2021-12-13 23:17', '2021-12-13 23:59', NULL, NULL, 0.7, 'solo/add'),
(5, 100003, '2021-12-12 05:50', '2021-12-12 11:00', '2021-12-12 11:30', 30, 5.17, 'solo/add'),
(6, 100003, '2021-12-12 11:30', '2021-12-12 14:25', '2021-12-13 05:51', 926, 2.92, 'no add'),
(7, 100003, '2021-12-13 05:51', '2021-12-13 11:05', '2021-12-13 11:36', 31, 5.23, 'solo/add'),
(8, 100003, '2021-12-13 11:36', '2021-12-13 14:25', NULL, NULL, 2.81, 'solo/add')
)
t1
(id, EmployeeID, punch_start, punch_end, next_punch_start, MinuteDiff, punch_hr, Decide)
The Excel file's screenshot shows the expected output in "ConsecutiveHours" column.
So, on this example, there are two incidents where two punch_hours were added together (illustrated in green and bold):
0.47 3.12 = 3.59
5.23 2.81 = 8.04
I have two different employees here and id was created (ordered) by EmployeeID and punch_start asc.
How do we go about writing this logic in T-SQL?
CodePudding user response:
You need to group those consecutive rows together. You can use window function LAG()
to identify. Once you have that, perform a cumulative sum partition by Employee and the group
with cte as
(
select *,
g = case when Decide
<> lag(Decide, 1, '') over (partition by EmployeeID
order by punch_start)
then 1
else 0
end
from #temp
),
cte2 as
(
select *, grp = sum(g) over (partition by EmployeeID order by punch_start)
from cte
)
select *,
Hours = sum(punch_hr) over (partition by EmployeeID, grp order by punch_start)
from cte2