This is a just couple of queries rolled into one.

This first query finds the start points of the intervals by finding all start points that don't lie inside an earlier interval.

SELECT s1.well_id,

s1.Top1

FROM TEMP1 s1

WHERE NOT EXISTS(SELECT * FROM TEMP1 s2

WHERE s2.well_id=s1.well_id

AND s1.Top1 > s2.Top1 AND s1.Top1 <= s2.BASE1)

This is the same idea but for end points instead.

SELECT t1.well_id,

t1.BASE1

FROM TEMP1 t1

WHERE NOT EXISTS(SELECT * FROM TEMP1 t2

WHERE t2.well_id=t1.well_id

AND t1.BASE1 >= t2.Top1 AND t1.BASE1 < t2.BASE1)

Finally, the start and end points are paired up by joining on well_id and finding the earliest end point for a start point, hence the GROUP BY and MIN. You could equally do this pairing up using ROW_NUMBER.

The "FOR XML" bit is a technique to concatenate rows, you'll find plenty of other examples of it in posts on SSC.

____________________________________________________

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

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537