This is based on an update that Itzik made New Solution to the Packing Intervals Problem
WITH interval_starts AS
(
SELECT *, CASE WHEN tFrom > LAG(tUntil, 1, '1900') OVER(PARTITION BY ID ORDER BY tFrom, tUntil) THEN 1 ELSE 0 END AS is_start
FROM #TestTable tt
)
, interval_groups AS
(
SELECT *, SUM(is_start) OVER(PARTITION BY ID ORDER BY tFrom, tUntil ROWS UNBOUNDED PRECEDING) AS grp
FROM interval_starts i
)
SELECT ID, MIN(tFrom) AS tFrom, MAX(tUntil) AS tUntil
FROM interval_groups g
GROUP BY ID, grp
ORDER BY ID, tFrom
It appears that it runs ever so slightly faster, and I think that's because it has one fewer sort. (I skipped the ROW_NUMBER(), so I don't have to sort to get the row number and then sort on the row number.)
I don't have time to set up an extensive test bed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA