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