Posted by Erland Sommarskog on 11/11/06 10:31

(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

• Next in forum: Re: Rolling up spans without breaks between them
• Prev in forum: Re: Instance Licencing
• Thread view: Re: Rolling up spans without breaks between them

[Reply to this message]