SQL: Merge Date Ranges

Question

I've a table, which describes work slices of a business working calendar: (date format is 24 hours format)

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 02:00   | 2012/07/21 04:00
2   | 2012/07/21 03:00   | 2012/07/21 10:00
3   | 2012/07/21 06:00   | 2012/07/21 17:00
4   | 2012/07/21 18:00   | 2012/07/21 19:00

Now, I like to merge the date ranges (within a given start and end date) like this:

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 02:00   | 2012/07/21 17:00
2   | 2012/07/21 18:00   | 2012/07/21 19:00

Is there a way to do this with SQL97 standard? If so, what is with other operations (e.g. if I want to to an invered merge, the result should be

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 00:00   | 2012/07/21 02:00
2   | 2012/07/21 19:00   | 2012/07/22 00:00

Welcome to Stack Overflow. What database and version are you using? — Mark Byers, Jul 25 '12 at 15:30
You intervals are overlapping, not consecutive. This is fairly hard to solve without windows/analytic functions. Why are you restricting yourself to the SQL 97 standard in 2012? — Gordon Linoff, Jul 25 '12 at 15:44
I have to restrict to SQL97 because we are currently using Oracle 10g and are going to switch to DB2 in future... — user1552089, Jul 26 '12 at 08:43

score 8 · Answer 1 · answered Jul 25 '12 at 15:47

8

Here's an example using SQL Server syntax. First it determines the "heads", or rows that have no previous overlapping rows. To determine the last "child" of a "head", it looks for the last row that is smaller than the next "head". Here's the SQL:

; with  heads as
        (
        select  row_number() over (order by head.StartDate) as PK
        ,       *
        from    YourTable head
        where   not exists 
                (
                select  *
                from    YourTable prev
                where   prev.StartDate < head.StartDate
                        and head.StartDate < prev.EndDate
                )
        )
select  row_number() over (order by h.StartDate) as PK
,       h.StartDate
,       max(yt.EndDate) as EndDate
from    heads h
left join
        heads nh
on      nh.PK = h.PK + 1
left join
        YourTable yt
on      h.StartDate <= yt.StartDate
        and (yt.StartDate < nh.StartDate or nh.StartDate is null)
group by
        h.StartDate

Live example at SQL Fiddle.

answered Jul 25 '12 at 15:47

Andomar

232,371
49
380
404

This kind of problem needs a recursion to evaluate an undetermined number of overlapping ranges. You can check it using the following intervals as an input of your quey 1) from 02:00 to 04:00, 2) from 03:00 to 10:00, 3) from 09:00 to 12:00, 4) from 11:00 to 17:00, 5) from 18:00 to 19:00. – Alessandro Rossi Feb 11 '16 at 11:02
@AlessandroRossi: I've tested your input and it works fine: returns 2-17 and 18-19 as the merged ranges – Andomar Feb 11 '16 at 11:52
Yes sorry, I didn't spot you used Tabibitosan Method. +1 – Alessandro Rossi Feb 11 '16 at 13:12
Excellent solution!!! The only adjustment I required is that if the end date of one range is equal to the start date of another, I wanted it to merge. So I changed "and head.StartDate < prev.EndDate" to "head.StartDate <= prev.EndDate". Everything else works fine. – Michael Erickson Jun 07 '20 at 06:40

score 0 · Answer 2 · answered Dec 03 '14 at 07:46

This is my solution.

IF OBJECT_ID('tempdb..#tblDates') IS NOT NULL
    DROP TABLE #tblDates

CREATE TABLE #tblDates (AutoId INT IDENTITY, StartDate DATE, EndDate DATE)

INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-02', '2014-11-08'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-07', '2014-11-10'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-06', '2014-11-12'

INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-02', '2014-11-15'

INSERT #tblDates (StartDate, EndDate) SELECT '2014-12-10', '2014-12-13'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-12-12', '2014-12-15'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-12-14', '2014-12-16'


-- Optional / Remove the duplicated records of same StartDate and EndDate
DELETE FROM #tblDates WHERE AutoId NOT IN (SELECT MAX(AutoId) FROM #tblDates GROUP BY StartDate, EndDate)

-- Optional / Get only the record with max EndDate grouped by StartDate, Remove Others
DELETE  d1
FROM    #tblDates d1
        JOIN (SELECT x.StartDate, MAX(x.EndDate) MAXEndDate FROM #tblDates x GROUP BY x.StartDate) d2 ON d2.StartDate = d1.StartDate AND d2.MAXEndDate != d1.EndDate

-- Optional / Get only the record with min StartDate grouped by EndDate, Remove Others
DELETE  d1
FROM    #tblDates d1
        JOIN (SELECT x.EndDate, MIN(x.StartDate) MINStartDate FROM #tblDates x GROUP BY x.EndDate) d2 ON d2.EndDate = d1.EndDate AND d2.MINStartDate != d1.StartDate

-- Optional / Remove the overlapping ranges of relevant StartDate and EndDate
DELETE  c
FROM    #tblDates p
        JOIN #tblDates c ON c.AutoId != p.AutoId AND c.StartDate BETWEEN p.StartDate AND p.EndDate AND c.EndDate BETWEEN p.StartDate AND p.EndDate


;WITH Ranges
AS
(
    SELECT  s.StartDate, s.EndDate
    FROM    #tblDates s
            LEFT JOIN #tblDates a ON a.AutoId != s.AutoId AND s.StartDate BETWEEN a.StartDate AND a.EndDate AND s.StartDate != a.StartDate
    WHERE   a.AutoId IS NULL
    UNION ALL
    SELECT  r.StartDate, d.EndDate
    FROM    Ranges r
            JOIN #tblDates d ON r.EndDate != d.EndDate AND r.EndDate BETWEEN d.StartDate AND d.EndDate
)

SELECT StartDate, MAX(EndDate) EndDate FROM Ranges GROUP BY StartDate

score 0 · Answer 3 · answered Aug 07 '17 at 14:36

Based on ErikE response :

IF(object_id('dbo.Periods') is not null)
    drop table Periods

go
create table Periods (
    StartDate date not null,
    EndDate date not null
)
go
insert into Periods(StartDate,EndDate)
select '1980-01-01','1980-01-10' union all
select '1980-01-03','1980-01-07' union all

select '2000-01-01','2000-01-10' union all
select '2000-01-05','2000-01-30' union all
select '2000-01-12','2000-01-20' union all

select '2021-01-01','2021-01-01'
go

; with LeadLag AS (
   SELECT     
     rownum = row_number() OVER( ORDER BY StartDate),
     PrevEndDate = Coalesce(Lag(EndDate) OVER (ORDER BY StartDate), Convert(datetime2, '0001-01-01')), 
     p.*
   FROM Periods p
), Dates AS (
   SELECT
        StartDate = CASE WHEN PrevEndDate < StartDate THEN StartDate ELSE NULL END,
        EndDate,           
        rownum
   FROM   LeadLag
), startGrouping AS (
   SELECT
      StartDate =  max(StartDate) OVER (ORDER BY rownum rows UNBOUNDED PRECEDING),
      EndDate,
      rownum
   FROM Dates
),
 groups AS (
   SELECT
      StartDate,
      EndDate,
      rownum,
      ingroupRownum = row_number() OVER(PARTITION BY StartDate ORDER BY EndDate desc)
   FROM startGrouping e1
)
SELECT StartDate, EndDate
from groups
WHERE  ingroupRownum = 1

SQL: Merge Date Ranges

3 Answers3

Linked

Related