Producing islands of times throughout a time range. SQL Server

Question

Please help me I have a bit of an odd SQL issue.

So if you assume the standard office hours are between 9:00 and 17:00.

I have a list of breaks, and I would like to add those breaks to my working day and return a collection of times when I am available.

This SQL shows my working hours, my breaks and what I would like the output to be:

DECLARE @WorkingHours TABLE
   (StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)
insert @WorkingHours select '09:00',  '17:00', '2018-08-15', 0;

DECLARE @Breaks TABLE
   (StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)

insert @Breaks select '11:30',  '12:30', '2018-08-15', 1;
insert @Breaks select '12:00',  '13:00', '2018-08-15', 1;
insert @Breaks select '15:00',  '16:00', '2018-08-15', 1;
insert @Breaks select '15:25',  '15:55', '2018-08-15', 1;
insert @Breaks select '09:50',  '10:05', '2018-08-15', 1;
insert @Breaks select '15:50',  '16:05', '2018-08-15', 1;

DECLARE @Output TABLE
   (StartTime TIME, EndTime TIME, EventDate date)

insert @Output select '09:00',  '09:50', '2018-08-15';
insert @Output select '10:05',  '11:30', '2018-08-15';
insert @Output select '13:00',  '15:00', '2018-08-15';
insert @Output select '16:05',  '17:00', '2018-08-15';

SELECT * FROM @Output

This is the closest I've managed to come, but it doesn't handle overlapping breaks, or breaks within breaks.

DECLARE @Final TABLE
   (StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)

INSERT INTO @Final
SELECT * FROM @WorkingHours
UNION
SELECT * FROM @Breaks

SELECT CASE WHEN t1.IsBreak = 0 THEN t1.StartTime  
            ELSE t1.EndTime 
        END AS StartTime , 
       CASE WHEN LEAD(t1.EventDate) OVER 
                   ( ORDER BY t1.EventDate, 
                              t1.[StartTime]
                   ) = t1.EventDate THEN 
                     coalesce(Lead(t1.StartTime) OVER
                       ( ORDER BY t1.EventDate, 
                                  t1.[StartTime]), 
                                  '17:00'
                       ) 
            ELSE '17:00' 
        END AS EndTime, 
        t1.EventDate 
  FROM  @Final  t1 
 INNER 
  JOIN @Final t2 
    ON t1.EventDate = t2.EventDate 
   AND t2.IsBreak = 0 

Any help anyone could offer will be greatly appreciated.

Answer 1

I don't know if this is an overthink, but it should handle any combination of breaks

DECLARE @WorkingHours TABLE
   (StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)
insert @WorkingHours select '09:00',  '17:00', '2018-08-15', 0;

DECLARE @Breaks TABLE
   (StartTime TIME, EndTime TIME, EventDate date, IsBreak bit)

insert @Breaks select '11:30',  '12:30', '2018-08-15', 1;
insert @Breaks select '12:00',  '13:00', '2018-08-15', 1;
insert @Breaks select '15:00',  '16:00', '2018-08-15', 1;
insert @Breaks select '15:25',  '15:55', '2018-08-15', 1;
insert @Breaks select '09:50',  '10:05', '2018-08-15', 1;
insert @Breaks select '15:50',  '16:05', '2018-08-15', 1;

DECLARE @Output TABLE
   (StartTime TIME, EndTime TIME, EventDate date)

insert @Output select '09:00',  '09:50', '2018-08-15';
insert @Output select '10:05',  '11:30', '2018-08-15';
insert @Output select '13:00',  '15:00', '2018-08-15';
insert @Output select '16:05',  '17:00', '2018-08-15';

SELECT * FROM @Output

DECLARE @DATE as date = '20180815';

;WITH cHours as (SELECT 0 H
                UNION ALL 
                SELECT H + 1 FROM cHours WHERE H < 23),
     cMins as (SELECT 0 M
                UNION ALL 
                SELECT M + 1 FROM cMins WHERE M < 59),
     cMinDay as (SELECT CAST(dateadd(minute,H*60 + M,0) as time) aTime FROM cHours CROSS JOIN cMins),
     cActiveMins as (
                        SELECT aTime ,  
                                    CASE WHEN 
                                                EXISTS(SELECT 0 FROM @WorkingHours w WHERE w.StartTime <= a.aTime  and a.aTime < w.EndTime)
                                                    AND NOT EXISTS(SELECT 0 FROM @Breaks  b WHERE b.StartTime <= a.atime and a.atime < b.EndTime)
                                                 THEN 1 ELSE 0 
                                    END WorkFlag 

                                    FROM cMinDay a 
                    ),
     cIsland as (SELECT *,(row_number() OVER (ORDER BY atime)) -    (row_number() OVER (ORDER BY workflag, atime)) x FROM   cActiveMins)                                                                        

     select workflag, MIN(atime),dateadd(minute,1,MAX(atime)) from cIsland GROUP BY workflag,x  having workflag=1;