Merge overlapping date intervals

by

I was looking for the same solution and came across this post on Combine overlapping datetime to return single overlapping range record.

There is another thread on Packing Date Intervals.

I tested this with various date ranges, including the ones listed here, and it works correctly every time.

SELECT 
       s1.StartDate,
       --t1.EndDate 
       MIN(t1.EndDate) AS EndDate
FROM @T s1 
INNER JOIN @T t1 ON s1.StartDate <= t1.EndDate
  AND NOT EXISTS(SELECT * FROM @T t2 
                 WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate) 
WHERE NOT EXISTS(SELECT * FROM @T s2 
                 WHERE s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate) 
GROUP BY s1.StartDate 
ORDER BY s1.StartDate

The result is:

StartDate  | EndDate
2010-01-01 | 2010-06-13
2010-06-15 | 2010-06-25
2010-06-26 | 2010-08-16
2010-11-01 | 2010-12-31

More Related Contents:

  1. DateTime2 vs DateTime in SQL Server
  2. How to generate a range of numbers between two numbers?
  3. How to select all records from one table that do not exist in another table?
  4. SET versus SELECT when assigning variables?
  5. T-SQL dynamic pivot
  6. Avoid duplicates in INSERT INTO SELECT query in SQL Server
  7. Generate Dates between date ranges
  8. Select group of rows that match all items in a list
  9. Add Foreign Key relationship between two Databases
  10. SQL Server check case-sensitivity?
  11. How to print VARCHAR(MAX) using Print Statement?
  12. How to assign an exec result to a sql variable?
  13. Concatenate values based on ID
  14. Query error with ambiguous column name in SQL
  15. Why (and how) to split column using master..spt_values?
  16. Splitting delimited values in a SQL column into multiple rows
  17. What does =* mean?
  18. SQL: parse the first, middle and last name from a fullname field
  19. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  20. SQL Select Upcoming Birthdays
  21. How can I include null values in a MIN or MAX?
  22. Stored procedure that exports data into csv files only exports to one file
  23. Get Start and End Date from Week Number SQL Server
  24. How to get the identity of an inserted row?
  25. Get the last day of the month in SQL
  26. Getting Dates between a range of dates
  27. The most elegant way to generate permutations in SQL server
  28. Cumulative Total in MS Sql server [duplicate]
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment