Hi,

Many thanks for the break down. Sorry to bother you I am just keen to get my head round this.

Taken another look at it this evening. I made a change to is to ensure that the overlapping numbers must be within the range as opposed to <= or >=

SELECT s1.well_id,

s1.Top1,

MIN(t1.BASE1) AS BASE1,

(SELECT distinct c.comment AS "text()"

FROM TEMP1 c

WHERE c.well_id = s1.well_id

AND c.Top1 BETWEEN s1.Top1 AND MIN(t1.BASE1)

AND c.BASE1 BETWEEN s1.Top1 AND MIN(t1.BASE1)

ORDER BY c.comment

FOR XML PATH(''),TYPE).value('.','VARCHAR(400)') AS comments

FROM TEMP1 s1

INNER JOIN TEMP1 t1 ON t1.well_id=s1.well_id

AND s1.Top1 < t1.BASE1

AND NOT EXISTS(SELECT * FROM TEMP1 t2

WHERE t2.well_id=t1.well_id

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

WHERE NOT EXISTS(SELECT * FROM TEMP1 s2

WHERE s2.well_id=s1.well_id

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

GROUP BY s1.well_id,s1.Top1;

I always find the best way I can understand it is to explain it, so here goes:

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)

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)

These two sub queries build up list of ranges where top>base (for Top values) and base < top (For Base Values)

Then these are joined by well_ID and min base to each top in the list - to be honest I am still not clear about how this part works any why you only need to choose min base and not max top. If you would be kind enough to show how it looks with row number that would be really helpful.

Then regarding the XML essentially this runs the same queries again but the XML is concatenating the comments field using the text() node in XML Path.

Really appreciate your help, sorry I am a slow learner.

Oliver