I have a table with three columns.
DAYOFWEEK, STARTTIME, and ENDTIME. For many of the rows (sorted by STARTTIME) the STARTTIME is equal to the previous ENDTIME. I need to create ranges where those times are concurrent.

Sample Data:

Based on the sample data I'm seeking:

I'm sure it can be done somehow with CTE's and/or LAG/LEAD but I just can't work it out. Any brilliant ideas out there? I'm limited to using SQL 2014. Thanks!

CodePudding user response：

This could work:

WITH
indata(dayofweek,starttime,endtime) AS (
          SELECT 1,61200,62100
UNION ALL SELECT 1,62100,63000
UNION ALL SELECT 1,63000,63900
UNION ALL SELECT 1,63900,64800
UNION ALL SELECT 1,64800,65700
UNION ALL SELECT 1,68800,69900
)
,
w_neighbours AS (
SELECT
    *
  , COALESCE(
      LAG(endtime   ) OVER (PARTITION BY dayofweek ORDER BY starttime)
    , starttime
    ) AS lastend
  , COALESCE(
      LEAD(starttime) OVER (PARTITION BY dayofweek ORDER BY starttime)
    , endtime  
    ) AS nextstart
  FROM indata
  -- ctl SELECT * from w_neighbours
  -- ctl  dayofweek | starttime | endtime | lastend | nextstart 
  -- ctl ----------- ----------- --------- --------- -----------
  -- ctl          1 |     61200 |   62100 |   61200 |     62100
  -- ctl          1 |     62100 |   63000 |   62100 |     63000
  -- ctl          1 |     63000 |   63900 |   63000 |     63900
  -- ctl          1 |     63900 |   64800 |   63900 |     64800
  -- ctl          1 |     64800 |   65700 |   64800 |     68800
  -- ctl          1 |     68800 |   69900 |   65700 |     69900
)
SELECT
  dayofweek
, starttime
, endtime
FROM w_neighbours
WHERE starttime - lastend > 0
   OR nextstart - endtime > 0
;
-- out  dayofweek | starttime | endtime 
-- out ----------- ----------- ---------
-- out          1 |     64800 |   65700
-- out          1 |     68800 |   69900