Posted by Erland Sommarskog on 11/10/06 23:01

(davisutt@aol.com) writes:
> 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.

This appears to work, but you should test it with more dates. Note
that I am allowing the intervals to overlap.

It may not be that very efficient.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: No Local instance with SQLServer2005 Dev. Ed.
• Prev in forum: Re: FOR XML not working in a subquery
• Thread view: Re: Rolling up spans without breaks between them

[Reply to this message]