Merge timesheet rows with no gap between start and finish times

Question

I'm working on a timesheeting system where employees work on various jobs throughout the day. I need to do a query of some sort that returns a merged version of the times so that if an employee works a full day without any breaks between jobs then I just get one row for that day. But if there is a gap between the end time of one row and the start time of the next row then this should return as a separate row. I'd also need to break hours and total hours summed up.

I hope that makes sense? I could probably do it with a cursor or something but that wouldn't be the most elegant approach.

DECLARE @Times TABLE (StartTime DATETIME, EndTime DATETIME, BreakHours DECIMAL(8,2), TotalHours DECIMAL(8,2))
INSERT INTO @Times (StartTime, EndTime, BreakHours, TotalHours)
VALUES ('2020-08-24 07:00','2020-08-24 08:30',0.00,1.50)
,('2020-08-24 08:30','2020-08-24 12:00',0.50,3.00)
,('2020-08-24 12:00','2020-08-24 16:00',0.00,4.00)
,('2020-08-24 16:00','2020-08-24 17:30',0.00,1.50)
,('2020-08-25 07:00','2020-08-25 08:30',0.00,1.50)
,('2020-08-25 08:30','2020-08-25 11:45',0.50,2.75)
,('2020-08-25 12:00','2020-08-25 16:00',0.00,4.00)
,('2020-08-25 16:00','2020-08-25 17:45',0.00,1.75)
,('2020-08-25 23:00','2020-08-26 05:00',0.50,5.50)

And this is the kind of result set I'd like to see...

StartTime           EndTime             BreakHours  TotalHours
2020-08-24 07:00    2020-08-24 17:30    0.50        10.00
2020-08-25 07:00    2020-08-25 11:45    0.50        4.25
2020-08-25 12:00    2020-08-25 17:45    0.00        5.75
2020-08-25 23:00    2020-08-26 05:00    0.50        5.50

score 0 · Accepted Answer · answered Sep 03 '20 at 07:32

This is a gaps and islands problem. One approach uses lag() to retrieve the "previous" end date and a window sum() that increments everytime a gap is met. This defines groups of adjacent rows, that you can then aggregate:

select 
    min(starttime) starttime,
    max(endtime) endtime,
    sum(breakhours) breakhours,
    sum(totalhours) totalhours
from (
    select t.*, sum(case when starttime = lag_endtime then 0 else 1 end) over(order by starttime) grp
    from (
        select t.*, lag(endtime) over(order by starttime) lag_endtime
        from mytable t
    ) t
) t
group by grp

Your data seems to be missing a column that identifies an employee. If you had one, you would typically add it as a partition to the over() clause of the window functions, and to the group by clause of the outer query.

Merge timesheet rows with no gap between start and finish times

1 Answers1