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.

The breaks belong to different members of staff. So the final available time is that of a piece of machinery. Which staff members share. — Daniel Frappell, Aug 02 '18 at 14:54

score 3 · Answer 1 · answered Aug 02 '18 at 15:26

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;

the was a bug on the last line that I changed – Cato Aug 02 '18 at 15:27 — Cato, Aug 02 '18 at 15:27

score 1 · Answer 2 · edited Aug 02 '18 at 16:50

The desired output can be obtain by filtering results of your last query with StartTime < EndTime , with a cte for example :

WITH cte AS ( 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' 
--           (
--               SELECT MAX(EndTime)
--               FROM @Final
--               WHERE IsBreak = 0
--                     AND [@Final].EventDate = t1.EventDate
--           )

                             END AS EndTime
                           , t1.EventDate
                  FROM       @Final t1
                  INNER JOIN @Final t2
                          ON t1.EventDate = t2.EventDate
                         AND t2.IsBreak   = 0 )
    SELECT *
    FROM   cte
    WHERE  StartTime < EndTime

@DanielFrappell I think this code needs a slight touch. I am adding it as a comment in SQL. — Cetin Basoz, Aug 02 '18 at 16:46
@DanielFrappell, actually needs more correction than that, hope you can get it right. — Cetin Basoz, Aug 02 '18 at 16:52
Hi, yes it actually started to break down with more complex scenarios. I think @cato solution has got it. Albeit it, it looks more complicated. Thank you for your help though. — Daniel Frappell, Aug 03 '18 at 14:45

score 0 · Answer 3 · answered Aug 02 '18 at 15:20

I recently solved a similar problem.

UNPIVOT your StartDate and EndDate into a single column. Then anywhere a StartDate or EndDate isn't seen exactly 2 times means you don't have contiguous data, so it's either the open interval at the start or end, or a missing data point (an open interval inbetween the outermost open intervals).

Cheers.

; WITH dv AS (
    SELECT *
    FROM #dv
        UNPIVOT ( DateDA FOR dATES IN (StartDate, EndDate)
), dv2 AS (
    SELECT j1.datavalueid AS [j1_datavalueid],
        j1.startdate AS [j1_startdate],
        j1.EndDate AS [j1_EndDate],
        j2.datavalueid AS [j2_datavalueid],
        j2.startdate AS [j2_startdate],
        j2.EndDate AS [j2_EndDate]
    FROM
        dv j1
        FULL OUTER JOIN dv j2
            ON  j1.StartDate = j2.EndDate
    WHERE (j1.DataValueID IS NULL
        OR j2.DataValueID IS NULL)
)
SELECT
    COALESCE(j1_datavalueid, j2_datavalueid) AS datavalueid,
    j2_endDate AS startdate,
    j1_startdate AS enddate
FROM
    dv2
ORDER BY COALESCE(j2_enddate, j1_enddate)

Producing islands of times throughout a time range. SQL Server

3 Answers3