Find gaps in start stop times on mutliple overlapping rows

harishgg1 · April 15, 2026, 4:37am

hi

greater than 15 min intervals

hope this helps

create sample data script

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 (Employe	eFK, 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 Blocks AS (
    SELECT EmployeeFK,
           MIN(TimeOn) AS BlockStart,
           MAX(TimeOff) AS BlockEnd
    FROM WorkOrderCollection
    GROUP BY EmployeeFK, TimeOn
),
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 Blocks
)
SELECT EmployeeFK, BlockStart, BlockEnd, NextStart, GapMinutes
FROM Gaps
WHERE GapMinutes > 15
ORDER BY EmployeeFK, BlockStart;

Data

t-sql Result