MySQL - Merge overlapping date intervals

Found in [https://stackoverflow.com/questions/2561130/merge-overlapping-date-intervals]

Merge overlapping date intervals

Is there a better way of merging overlapping date intervals? The solution I came up with is so simple that now I wonder if someone else has a better idea of how this could be done. /***** DATA EXAMPLE *****/ DECLARE @T TABLE (d1 DATETIME, d2 DATETIME) INSERT INTO @T (d1, d2) SELECT '2010-01-01','2010-03-31' UNION SELECT '2010-04-01','2010-05-31' UNION SELECT '2010-06-15','2010-06-25' UNION SELECT '2010-06-26','2010-07-10' UNION SELECT '2010-08-01','2010-08-05' UNION SELECT '2010-08-01','2010-08-09' UNION SELECT '2010-08-02','2010-08-07' UNION SELECT '2010-08-08','2010-08-08' UNION SELECT '2010-08-09','2010-08-12' UNION SELECT '2010-07-04','2010-08-16' UNION SELECT '2010-11-01','2010-12-31' UNION SELECT '2010-03-01','2010-06-13' /***** INTERVAL ANALYSIS *****/ WHILE (1=1) BEGIN UPDATE t1 SET t1.d2 = t2.d2 FROM @T AS t1 INNER JOIN @T AS t2 ON DATEADD(day, 1, t1.d2) BETWEEN t2.d1 AND t2.d2 IF @@ROWCOUNT = 0 BREAK END /***** RESULT *****/ SELECT StartDate = MIN(d1) , EndDate = d2 FROM @T GROUP BY d2 ORDER BY StartDate, EndDate /***** OUTPUT *****/ /***** StartDate EndDate 2010-01-01 2010-06-13 2010-06-15 2010-08-16 2010-11-01 2010-12-31 *****/  (...)

12down vote

I was looking for the same solution and came across this post. There is also another thread here. I tested this with various date ranges including the ones listed here and it works correclty 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 shareimprove this answer edited Nov 6 '12 at 23:26 ForceMagic 3,49283870 answered Nov 14 '11 at 10:45 user1045402 12114

Also, found another example with explanation on how to achieve this here: sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/… – user1045402 Nov 23 '11 at 11:50  You can edit your own answer to add more information, just click on the "edit" link at the bottom of your answer. – ForceMagic Nov 6 '12 at 23:15 Works perfect and is concise! – ensisNoctis Sep 8 '16 at 12:41 add a comment