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