Posted by davisutt on 11/10/06 23:20

Erland Sommarskog wrote:
> (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

Thanks for the reply.

I'm using IE to view this so maybe it's my viewer's fault but I was not
able to see your script...only your disclaimer comments. :)

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

[Reply to this message]