SQL Date Range Split

Question

Can you please let me know the SQL to split date ranges when they overlap?

Data (sample data with a date range and possibly other columns):

    Col1 FromDate ToDate
 1. 1    1/1/2008 31/12/2010
 2. 1    1/1/2009 31/12/2012
 3. 1    1/1/2009 31/12/2014

Output:

   Col1  From Date ToDate
1. 1     1/1/2008 31/12/2008 (from row 1 above)
2. 1     1/1/2009 31/12/2010 (from rows 1,2 and 3 above)
3. 1     1/1/2011 31/12/2012 (from rows 2 and 3 above)
4. 1     1/1/2013 31/12/2014 (from row 3 above)

Thx.

Answer 1

Ok, I don't have the complete answer, but here are some points to consider.

1. The first block of time is

   select min(FromDate) from table group by FromDateprint("code sample");

2. Then end of the first block is

   select min(FromDate) from table group by FromDate where FromDate > "start of block"

3. Repeat as needed. :)

Answer 2

This should do the trick (MySQL dialect, but easily adaptable)

Initial setup

SQL query: SELECT * FROM `test` LIMIT 0, 30 ;
Rows: 3
start       end
2008-01-01  2010-12-31
2009-01-01  2012-12-31
2009-01-01  2014-12-31

Query

SELECT 
  `start` , min( `end` )
FROM (
  SELECT t1.start, t2.end
  FROM test t1, test t2
  WHERE t1.start < t2.end
  UNION
  SELECT t1.end + INTERVAL 1 DAY , t2.end
  FROM test t1, test t2
  WHERE t1.end + INTERVAL 1 DAY < t2.end
  UNION
  SELECT t1.start, t2.start - INTERVAL 1 DAY
  FROM test t1, test t2
  WHERE t1.start < t2.start - INTERVAL 1 DAY
) allRanges
GROUP BY `start`

Result

start       min( `end` )
2008-01-01  2008-12-31
2009-01-01  2010-12-31
2011-01-01  2012-12-31
2013-01-01  2014-12-31

EDIT: Fixed bug as mentioned by author.

Answer 3

Skliwz's answer adapted for SQL Server:

DECLARE @DateTest TABLE 
(
    FromDate datetime,
    ToDate datetime 
)

insert into @DateTest (FromDate, ToDate)
(
select cast('1/1/2008' as datetime), cast('12/31/2010' as datetime)
union
select cast('1/1/2009' as datetime), cast('12/31/2012' as datetime)
union
select cast('1/1/2009' as datetime), cast('12/31/2014' as datetime)
)

SELECT 
  FromDate , min(ToDate)
FROM (
  SELECT t1.FromDate, t2.ToDate
  FROM 
    @DateTest t1, 
    @DateTest t2
  WHERE t1.FromDate < t2.ToDate

  UNION

  SELECT dateadd(DAY, 1, t1.ToDate), t2.ToDate
  FROM 
    @DateTest t1, 
    @DateTest t2
  WHERE dateadd(DAY, 1, t1.ToDate) < t2.ToDate
) allRanges
group by FromDate

Answer 4

2008-01-01 00:00:00.000 **2010-12-31 00:00:00.000** <-- This end date is correct.
2009-01-01 00:00:00.000 2010-12-31 00:00:00.000
2011-01-01 00:00:00.000 2012-12-31 00:00:00.000
2013-01-01 00:00:00.000 2014-12-31 00:00:00.000

The result still has overlapping dates. The first one should have end date as 2008-12-31, as specified in the original sample output. Instead, it shows 2010-12-31.

Please let me know what is wrong.

Answer 5

What would your expected results be for a period wholly contained by another period? What about two duplicate periods (same start date and end date)?

What's puzzling me is that, considering all the periods in the sample data overlap, why they are they not consolidated into one single period in the expected results? i.e.

'2008-01-01T00:00:00.000' '2014-12-31T00:00:00.000'