|
|
Posted by Ed Murphy on 10/10/06 02:34
markc600@hotmail.com wrote:
> SELECT s1.Start,
> MIN(t1.Stop) AS Stop
> FROM Intervals s1
> INNER JOIN Intervals t1 ON s1.Start <= t1.Stop
> AND NOT EXISTS(SELECT * FROM Intervals t2
^^^
> WHERE t1.Stop BETWEEN t2.Start AND t2.Stop
> AND t2.Stop > t1.Stop)
> WHERE NOT EXISTS(SELECT * FROM Intervals s2
^^^^^
> WHERE s1.Start BETWEEN s2.Start AND s2.Stop
> AND s2.Start < s1.Start)
> GROUP BY s1.Start
The indicated AND and WHERE need to be swapped, but otherwise this
appears to work. Translating to English:
1) Match each starting point with each ending point that exceeds it.
2) Eliminate all cases where the ending point could be pushed higher
by merging with another interval.
3) Eliminate all cases where the starting point could be pushed lower
by merging with another interval.
4) For each starting point, cut the list down to the lowest matched
ending point that hasn't been eliminated yet. (All other matched
ending points belong to a different merged-interval.)
Personally, I would replace
t1.Stop BETWEEN t2.Start AND t2.Stop AND t2.Stop > t1.Stop
with
t2.Start <= t1.Stop AND t2.Stop > t1.Stop
and similarly for the other WHERE ... BETWEEN.
Navigation:
[Reply to this message]
|