SQL server: How to build a date range from table with start and end times

Question

I have a temp table containing 24 entries, one for each hour of a specific day annotated by DayID. The table contains an indication whether an application is on or off during that specific time range.

I would like to reduce the number of entries in the table to only show the times during which the application should be on.

Some sample data follows:

CREATE TABLE #OnOff 
(
    RowID INT identity(1, 1), 
    DayID TINYINT, 
    OnOff BIT, 
    StartTime TIME, 
    EndTime TIME
)

INSERT INTO #OnOff (DayID, OnOff, StartTime, EndTime) VALUES
(1, 0, '00:00', '00:59'),
(1, 0, '01:00', '01:59'),
(1, 0, '02:00', '02:59'),
(1, 1, '03:00', '03:59'),
(1, 1, '04:00', '04:59'),
(1, 0, '05:00', '05:59'),
(1, 1, '06:00', '06:59'),
(1, 1, '07:00', '07:59'),
(1, 0, '08:00', '08:59'),
(1, 0, '09:00', '09:59'),
(1, 0, '10:00', '10:59'),
(1, 0, '11:00', '11:59'),
(1, 0, '12:00', '12:59'),
(1, 0, '13:00', '13:59'),
(1, 1, '14:00', '14:59'),
(1, 1, '15:00', '15:59'),
(1, 1, '16:00', '16:59'),
(1, 0, '17:00', '17:59'),
(1, 0, '18:00', '18:59'),
(1, 0, '19:00', '19:59'),
(1, 0, '20:00', '20:59'),
(1, 0, '21:00', '21:59'),
(1, 0, '22:00', '22:59'),
(1, 0, '23:00', '23:59')

The desired output should be

DayID   StartTime  EndTime
1       03:00      04:59
1       06:00      07:59
1       14:00      16:59

The #OnOff table will always contain a value for every hour of the day (ie end and start times will always be consecutive, except for the last hour). One can obviously achieve this with a cursor, but this seems quite inefficient. Is there a better way to achieve this result.

Your question is not very clear. why not just insert only the rows where the value of `OnOff` is 1? — Zohar Peled, Jul 18 '16 at 09:46
Beware using the time ranges you've shown.... `timenow < ='16:59'` differs from `timenow < 17:00` by almost a minute. — O. Jones, Jul 18 '16 at 10:48
@Ollie Thanks your comment ... well noted. In this instance frequency is never less than 60s and can be managed, but I agree with the principle raised — Gert, Jul 18 '16 at 10:58
Right, I have been burned by this sort of off-by-59 error a few times. — O. Jones, Jul 18 '16 at 11:23

score 2 · Answer 1 · answered Jul 18 '16 at 10:25

You can achieve the desired results by using a CTE over the dataset:

CREATE TABLE #OnOff
    (
      RowID INT IDENTITY(1, 1) ,
      DayID TINYINT ,
      OnOff BIT ,
      StartTime TIME ,
      EndTime TIME
    )

INSERT  INTO #OnOff
        ( DayID, OnOff, StartTime, EndTime )
VALUES  ( 1, 0, '00:00', '00:59' ),
        ( 1, 0, '01:00', '01:59' ),
        ( 1, 0, '02:00', '02:59' ),
        ( 1, 1, '03:00', '03:59' ),
        ( 1, 1, '04:00', '04:59' ),
        ( 1, 0, '05:00', '05:59' ),
        ( 1, 1, '06:00', '06:59' ),
        ( 1, 1, '07:00', '07:59' ),
        ( 1, 0, '08:00', '08:59' ),
        ( 1, 0, '09:00', '09:59' ),
        ( 1, 0, '10:00', '10:59' ),
        ( 1, 0, '11:00', '11:59' ),
        ( 1, 0, '12:00', '12:59' ),
        ( 1, 0, '13:00', '13:59' ),
        ( 1, 1, '14:00', '14:59' ),
        ( 1, 1, '15:00', '15:59' ),
        ( 1, 1, '16:00', '16:59' ),
        ( 1, 0, '17:00', '17:59' ),
        ( 1, 0, '18:00', '18:59' ),
        ( 1, 0, '19:00', '19:59' ),
        ( 1, 0, '20:00', '20:59' ),
        ( 1, 0, '21:00', '21:59' ),
        ( 1, 0, '22:00', '22:59' ),
        ( 1, 0, '23:00', '23:59' )

;WITH    cte
    AS ( -- Get the first row, seed for the cte, set the OnOffGroup to 1
        SELECT TOP 1
                RowID , DayID , OnOff , StartTime , EndTime , 1 AS OnOffGroup
        FROM     #OnOff
        WHERE    OnOff = 1
        ORDER BY RowID
        UNION ALL
        -- Join latest cte row with next row in sequence, 
        -- OnOffGroup set to previous row value if state matches, otherwise increment
        SELECT   #OnOff.RowID , #OnOff.DayID , #OnOff.OnOff , 
                 #OnOff.StartTime , #OnOff.EndTime ,
                CASE WHEN #OnOff.OnOff = 1 THEN cte.OnOffGroup
                        ELSE cte.OnOffGroup + 1
                END AS OnOffGroup
        FROM     #OnOff
                INNER JOIN cte ON cte.RowID + 1 = #OnOff.RowID
        )
    -- Output results with grouping and min/max to produce desired results
    SELECT  cte.DayID ,
            MIN(cte.StartTime) AS StartTime ,
            MAX(cte.EndTime) AS EndTime ,
            cte.OnOffGroup
    FROM    cte
    WHERE   cte.OnOff = 1
    GROUP BY cte.DayID ,
            cte.OnOffGroup

Produces:

DayID   StartTime           EndTime             OnOffGroup
1       03:00:00.0000000    04:59:00.0000000    1
1       06:00:00.0000000    07:59:00.0000000    2
1       14:00:00.0000000    16:59:00.0000000    8

score 1 · Answer 2 · answered Jul 18 '16 at 10:42

Another way with OUTER APPLY and DENSE_RANK:

;WITH cte AS (
SELECT  o.StartTime, 
        o.EndTime,
        DENSE_RANK() OVER (ORDER BY r.EndTime) as DR
FROM #OnOff o
OUTER APPLY (
            SELECT top 1 * 
            FROM #OnOff 
            WHERE o.EndTime < EndTime and OnOff = 0
            ) as r
WHERE o.OnOff = 1 
)

SELECT  MIN(StartTime) as StartTime,
        MAX(EndTime) as EndTime
FROM cte
GROUP BY DR

Output:

StartTime           EndTime
03:00:00.0000000    04:59:59.0000000
06:00:00.0000000    07:59:59.0000000
14:00:00.0000000    16:59:59.0000000

Thanks for your assistance. This worked well and was quite easy to add additional dayIDs for other days. — Gert, Jul 21 '16 at 12:48

SQL server: How to build a date range from table with start and end times

2 Answers2