Posted by Alex Kuznetsov on 11/10/06 23:34

davisutt@aol.com wrote:
> I'm trying to figure out how to this do with TSQL and preferably
> through joins rather than by using cursors or temp tables. I have
> multiple time spans for multiple individuals that I would like to
> rollup together where they don't actually have a gap in coverage.
>
> ID StartDate EndDate
> Z000001 01/01/1996 9/30/1996
> Z000001 10/01/1996 12/31/1998
> Z000001 01/01/1999 04/30/1999
> Z000001 01/01/2000 12/31/2000
> Z000001 01/01/2001 12/31/2001
>
> I would like to be able to "roll these up" in order to reflect the
> actual time frames so that it would like this:
>
> ID StartDate EndDate
> Z000001 01/01/1996 4/30/1999
> Z000001 01/01/2000 12/31/2001
>
> 4/30/1999 to 01/01/2000 is, of course, a legitimate break where there
> was a date difference of more than one day.
>
> Thanks in advance for any ideas.

Just the general idea, you can replace numbers with dates.

-- Assuming table numbers with 10K rows
CREATE TABLE Intervals(intFrom INT, intTo INT)
INSERT Intervals VALUES(1, 5)
INSERT Intervals VALUES(4, 7)
INSERT Intervals VALUES(8, 10)
INSERT Intervals VALUES(21, 35)
go
SELECT MIN(intFrom) intFrom, intTo FROM(
SELECT intFrom, MAX(intTo) intTo FROM(
SELECT i1.intFrom, i2.intTo FROM Intervals i1, Intervals i2
WHERE i1.intFrom <= i2.intFrom AND i1.intTo <= i2.intTo
AND (i2.intTo - i1.intFrom + 1) = (SELECT COUNT(*) FROM Numbers n
WHERE EXISTS(SELECT 1 FROM Intervals i WHERE n.Number BETWEEN
i.intFrom AND i.intTo)
AND n.Number BETWEEN i1.intFrom AND i2.intTo)
) t
GROUP BY intFrom
) t
GROUP BY intTo

intFrom intTo
----------- -----------
1 10
21 35

(2 row(s) affected)

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Back to original message]