Find gaps in start stop times on mutliple overlapping rows

Transact-SQL
1

I am trying to find if there are gaps in employee clocking. The challenge is that employees can clock onto multiple machines simultaneously without any easy sequential sorting.

Example Data for a single employee (including the result column)

TimeOn TimeOff Gap
06:44 08:08 GAP
06:44 15:48
06:49 07:10
08:44 09:31

My current code (adapted from a post on this forum, thanks) finds most examples of time gaps but not for this data set (from a single employee). A gap is flagged incorrectly as they were clocked in between 06:44 to 15:48 on another row. So the challenge is to not flag gaps when another line in the collection makes this invalid. Note that only a single day is cheked per run.

Select
  C.EmployeeFK
, C.TimeOn
, C.TimeOff
, Iif(Lead(C.TimeOn) over (Partition By C.EmployeeFK Order By C.TimeOff) > DateAdd(mi, 15, C.TimeOff),'GAP',null) 'Gap'
From WorkOrderCollection C

Is there a nice way to discover any gaps in the contiguous times?

Cheers

2

You could try excluding On/Off times that were entirely within the range of another On/Off time:

SELECT
C.EmployeeFK
, C.TimeOn
, C.TimeOff
, Iif(Lead(C.TimeOn) over (Partition By C.EmployeeFK Order By C.TimeOff) > DateAdd(mi, 15, C.TimeOff),'GAP',null) 'Gap'
FROM (
SELECT
C.EmployeeFK
, C.TimeOn
, C.TimeOff
FROM dbo.WorkOrderCollection C
WHERE
NOT EXISTS(SELECT * FROM dbo.WorkOrderCollection C2 WHERE C.EmployeeFK = C2.EmployeeFK AND C.TimeOn BETWEEN C2.TimeOn AND C2.TimeOff AND
C.TimeOff BETWEEN C2.TimeOn AND C2.TimeOff AND NOT (C.TimeOn = C2.TimeOn AND C.TimeOff = C2.TimeOff))
) AS

3

Thanks, that has done the job, I include my tweeked code for a broader use case, that limits to a single day via a datetime variable, in my case user provided.

SELECT
  E.FirstName
, E.LastName
, Cs.TimeOn
, Cs.TimeOff
, Iif(LEAD(Cs.TimeOn) OVER (PARTITION BY Cs.EmployeeFK ORDER BY Cs.TimeOff) > DATEADD(mi, 15, Cs.TimeOff),'GAP',null) 'Gap'
FROM (
SELECT
  C.EmployeeFK
, C.TimeOn
, C.TimeOff
FROM WorkOrderCollection C
WHERE Cast(C.DateStamp AS Date) = @Date AND 
NOT EXISTS(SELECT * FROM WorkOrderCollection C2 WHERE Cast(C2.DateStamp as Date) = @Date And C.EmployeeFK = C2.EmployeeFK AND C.TimeOn BETWEEN C2.TimeOn AND C2.TimeOff AND
C.TimeOff BETWEEN C2.TimeOn AND C2.TimeOff AND NOT (C.TimeOn = C2.TimeOn AND C.TimeOff = C2.TimeOff))
) AS Cs
LEFT JOIN [User] E ON Cs.EmployeeFK = E.UserPK
4

Fantastic. Thanks for the feedback!

8

That’s interesting and I might be able to do something based on the concept, but the requirement is to find where there are gaps greater than 15 minutes per employee which is not clear from your results. I’m not even sure you have any >15min gaps in your sample data?

12

Thanks, I must not be explaining the requirement clearly enough.

In your data for employee 1, there is only one gap in your data between, which is between 17:00 & 18:00.

Your results for 94 & 384 mins gaps are not valid because the gap between 07:10 to 08:44 is within 06:44 & 15:48. The gap between 15:48 and 16:00 is below 15 mins removing the gap between 09:31 & 16:00 returned.

From your input data I would expect only the last 3 rows of your results back as having gaps.

13

hi

hope this helps

please give your comments Vlad

just curious Russian ?

DROP TABLE IF EXISTS WorkOrderCollection;
CREATE TABLE WorkOrderCollection (
    WorkOrderID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeFK INT NOT NULL,
    TimeOn DATETIME NOT NULL,
    TimeOff DATETIME NOT NULL
);
INSERT INTO WorkOrderCollection (EmployeeFK, TimeOn, TimeOff) VALUES
(1,'2026-04-14 06:44','2026-04-14 08:08'),
(1,'2026-04-14 06:44','2026-04-14 15:48'),
(1,'2026-04-14 06:49','2026-04-14 07:10'),
(1,'2026-04-14 08:44','2026-04-14 09:31'),
(1,'2026-04-14 16:00','2026-04-14 17:00'),
(1,'2026-04-14 18:00','2026-04-14 19:00'),
(1,'2026-04-14 19:05','2026-04-14 20:00'),
(2,'2026-04-14 07:00','2026-04-14 09:00'),
(2,'2026-04-14 09:05','2026-04-14 10:00'),
(2,'2026-04-14 10:00','2026-04-14 12:00'),
(3,'2026-04-14 08:00','2026-04-14 09:00'),
(3,'2026-04-14 09:30','2026-04-14 10:00'),
(3,'2026-04-14 11:00','2026-04-14 12:00'),
(4,'2026-04-14 13:00','2026-04-14 15:00'),
(4,'2026-04-14 13:30','2026-04-14 14:30'),
(4,'2026-04-14 14:00','2026-04-14 15:30');
;WITH CTE AS (
    SELECT *,
           CASE 
               WHEN TimeOn <= LAG(TimeOff) OVER (PARTITION BY EmployeeFK ORDER BY TimeOn)
               THEN 0 ELSE 1 
           END AS IsNewBlock
    FROM WorkOrderCollection
),
Grouped AS (
    SELECT *,
           SUM(IsNewBlock) OVER (PARTITION BY EmployeeFK ORDER BY TimeOn ROWS UNBOUNDED PRECEDING) AS grp
    FROM CTE
),
MergedBlocks AS (
    SELECT EmployeeFK,
           MIN(TimeOn) AS BlockStart,
           MAX(TimeOff) AS BlockEnd
    FROM Grouped
    GROUP BY EmployeeFK, grp
),
Gaps AS (
    SELECT EmployeeFK,
           BlockStart,
           BlockEnd,
           LEAD(BlockStart) OVER (PARTITION BY EmployeeFK ORDER BY BlockStart) AS NextStart,
           DATEDIFF(MINUTE, BlockEnd, LEAD(BlockStart) OVER (PARTITION BY EmployeeFK ORDER BY BlockStart)) AS GapMinutes
    FROM MergedBlocks
)
SELECT EmployeeFK, BlockStart, BlockEnd, NextStart, GapMinutes
FROM Gaps
WHERE GapMinutes > 15
ORDER BY EmployeeFK, BlockStart;

image
image1382×251 9.28 KB

14

Hi

Mostly accurate, but there are some false positives. Here is a snapshot of one of those.

Gap reported between 07:30 & 08:10 but that is covered by 06:30 to 08:10

image
image583×119 2.87 KB

Not Russian, stage name :wink:

15

Vlad

does this look good ? please let me know

;WITH Ordered AS (
    SELECT *,
           MAX(TimeOff) OVER (
               PARTITION BY EmployeeFK
               ORDER BY TimeOn
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           ) AS PrevMaxEnd
    FROM WorkOrderCollection
),
Groups AS (
    SELECT *,
           SUM(CASE 
               WHEN TimeOn <= ISNULL(PrevMaxEnd, TimeOn) THEN 0 
               ELSE 1 
           END) OVER (
               PARTITION BY EmployeeFK 
               ORDER BY TimeOn
           ) AS grp
    FROM Ordered
),
Blocks AS (
    SELECT EmployeeFK,
           MIN(TimeOn) AS BlockStart,
           MAX(TimeOff) AS BlockEnd
    FROM Groups
    GROUP BY EmployeeFK, grp
),
Gaps AS (
    SELECT *,
           LEAD(BlockStart) OVER (PARTITION BY EmployeeFK ORDER BY BlockStart) AS NextStart,
           DATEDIFF(MINUTE, BlockEnd,
                    LEAD(BlockStart) OVER (PARTITION BY EmployeeFK ORDER BY BlockStart)) AS GapMinutes
    FROM Blocks
)
SELECT *
FROM Gaps
WHERE GapMinutes > 15
ORDER BY EmployeeFK, BlockStart;

image
image1181×212 7.24 KB

16

Thanks

Looks good from my initial testing.