Posted by davisutt on 11/11/06 15:21

Erland Sommarskog wrote:
> (davisutt@aol.com) writes:
> > 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. :)
>
> Sorry, it appears that I forgot the essential part. Here it is:
>
> CREATE TABLE periods (start datetime NOT NULL,
> stop datetime NOT NULL,
> CHECK (start < stop))
> go
> INSERT periods (start, stop)
> VALUES ('19960101', '19990930')
> INSERT periods (start, stop)
> VALUES ('19960801', '19981231')
> INSERT periods (start, stop)
> VALUES ('19990101', '19990430')
> INSERT periods (start, stop)
> VALUES ('20000101', '20001231')
> INSERT periods (start, stop)
> VALUES ('20010101', '20011231')
> go
> SELECT a.start, MIN(b.stop)
> FROM (SELECT start
> FROM periods a
> WHERE NOT EXISTS (SELECT *
> FROM periods b
> WHERE b.start < a.start
> AND b.stop >= dateadd(DAY, -1, a.start))) AS a
> CROSS JOIN
> (SELECT stop
> FROM periods a
> WHERE NOT EXISTS (SELECT *
> FROM periods b
> WHERE b.stop > a.stop
> AND b.start <= dateadd(DAY, 1, a.stop))) AS b
> WHERE a.start < b.stop
> GROUP BY a.start
> go
> DROP TABLE periods
>
>
>
>
>
> --
> 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

Well worth the wait. That was PERFECT. Many, many thanks for your
time. I had been relegated to using a WHILE statement to loop through
the records and test for a gap in time but wasn't able to return all
the spans...only one. Thanks again.

Have a great weekend.

• Next in forum: Re: How do i find indexes of the columns in SQL server 2000???
• Prev in forum: Re: Rolling up spans without breaks between them
• Thread view: Re: Rolling up spans without breaks between them

[Reply to this message]