How to add number on next row based on condition (consecutive hours calculation)

userZI7g 0
Viewed 19

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: enter image description here

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?

tsql
1 Answers

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
Squirrel0
Related