oliver.morris (9/17/2012)

---

I changed the code to this to sort that change. Is there any problem with this?

-- identify rows with/without overlaps

;WITH SplitData AS (

SELECT a.*,

Parent = CASE WHEN EXISTS (

SELECT 1

FROM TEMP1 b

WHERE b.well_id = a.well_id

AND ((b.Top1 > a.Top1 AND b.Top1 < a.Base1) OR (b.Base1 > a.Top1 AND b.Base1 < a.Base1))

AND NOT (b.Top1 = a.Top1 or b.Base1 = a.Base1)

) THEN 1 ELSE 0 END

FROM TEMP1 a

),

-- sequence the rows for each well_id, partitioned by whether or not there's an overlap

SequencedData AS (

SELECT *,

seq = ROW_NUMBER() OVER (PARTITION BY a.well_id, a.parent ORDER BY a.Top1, a.Base1)

FROM SplitData a

),

rCTE AS (

SELECT -- anchor: no subsequent overlaps, or first in a sequence

level = 1, seq, Parent,

well_id, Top1, BASE1, comment,

Newcomment = CAST(ISNULL(comment,'') AS VARCHAR(100)),

NewTop1 = Top1,

NewBase1 = BASE1

FROM SequencedData

WHERE seq = 1

UNION ALL

SELECT

level = lr.level + 1, tr.seq, tr.Parent,

tr.well_id, tr.Top1, tr.BASE1, tr.comment,

Newcomment = CAST(lr.Newcomment + ISNULL(tr.comment,'') AS VARCHAR(100)),

NewTop1 = CASE WHEN lr.NewTop1 < tr.Top1 THEN lr.NewTop1 ELSE tr.Top1 END,

NewBase1 = CASE WHEN lr.NewBase1 > tr.BASE1 THEN lr.NewBase1 ELSE tr.BASE1 END

FROM SequencedData tr

INNER JOIN rCTE lr

ON lr.well_id = tr.well_id AND tr.seq > lr.seq

AND (tr.Top1 BETWEEN lr.Top1 AND lr.Base1 OR tr.Base1 BETWEEN lr.Top1 AND lr.Base1)

)

SELECT well_id, NewTop1, NewBase1, Newcomment

FROM (

SELECT *, Maxlevel = MAX(level) OVER(PARTITION BY well_id)

FROM rCTE

) d

WHERE Parent = 0

OR level = Maxlevel

ORDER BY well_id, level

Many Thanks