Find gaps in timesheet data between certain hours, Sql Server

Find gaps in timesheet data between certain hours

create table #time (
    TimesheetId int not null
    , StartTime datetime not null
    , EndTIme datetime not null
);

insert into #time (TimesheetId, StartTime, EndTime)
    values (210, '2017-05-08 05:30:00.000', '2017-05-08 06:30:00.000')
         , (210, '2017-05-08 06:30:00.000', '2017-05-08 08:30:00.000')
         , (210, '2017-05-08 08:30:00.000', '2017-05-08 12:00:00.000')
         , (210, '2017-05-08 12:30:00.000', '2017-05-08 18:30:00.000')
         , (210, '2017-05-09 08:30:00.000', '2017-05-09 12:00:00.000')
         , (210, '2017-05-09 12:30:00.000', '2017-05-09 17:30:00.000')
         , (210, '2017-05-09 22:30:00.000', '2017-05-10 05:30:00.000')
         , (210, '2017-05-10 08:30:00.000', '2017-05-10 18:00:00.000');
WITH
  a AS(SELECT DATEADD(hh, DATEDIFF(dd, 0, StartTime) * 24 + 8, 0) t,
         TimesheetId FROM #time),
  b AS(SELECT * FROM #time UNION ALL SELECT TimesheetId, t, t FROM a UNION ALL
       SELECT TimesheetId, DATEADD(hh, 10, t), DATEADD(hh, 10, t) FROM a),
  c AS(SELECT TimesheetId,
         LAG(EndTime) OVER (
           PARTITION BY TimesheetId ORDER BY StartTime
         ) prev_fin,
         StartTime
       FROM b),
   d AS(SELECT *, DATEADD(hh, DATEDIFF(dd, 0, prev_fin) * 24 + 8, 0) beg,
          DATEADD(hh, DATEDIFF(dd, 0, prev_fin) * 24 + 18, 0) fin
        FROM c)
SELECT TimesheetId, prev_fin, StartTime
FROM d
WHERE prev_fin < StartTime AND
  ((prev_fin >= beg AND prev_fin < fin) OR
   (StartTime > beg AND StartTime <= fin));


run \| edit \| history \| help	0