RE: Combine overlapping datetime to return single overlapping range record

One Orange Chip

Points: 26944

November 30, 2009 at 3:24 am

Try this

SELECT s1.StartDate,

MIN(t1.EndDate) AS EndDate

FROM Overlapping s1

INNER JOIN Overlapping t1 ON s1.StartDate <= t1.EndDate

AND NOT EXISTS(SELECT * FROM Overlapping t2

WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate)

WHERE NOT EXISTS(SELECT * FROM Overlapping s2

WHERE s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate)

GROUP BY s1.StartDate

ORDER BY s1.StartDate

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum