Rolling up spans without breaks between them

[davisutt]

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.

[Erland Sommarskog]

(da******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[davisutt]

Erland Sommarskog wrote:

(da******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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. :)

[Alex Kuznetsov]

da******@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/

[Alex Kuznetsov]

Erland Sommarskog wrote:

(da******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland, I don't see your query either

[Erland Sommarskog]

(da******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[davisutt]

Erland Sommarskog wrote:

(da******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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.

[Magenta]

Here's a view for invoicing that I had that finds the gaps (i.e.,
places where there is more than one day between the end of one invoice
and the start of the next (by activityid)

Backwards (from your perspective) but it may help you
/************************************************** ***************************************
***** Version 20060328_1155
**
** This view will list the invoice id and activity id for all invoices
that have no
** existing previous invoice, e.g., there is no invoice for the
activity that has a
** period ending corresponding to a listed invoices's period
beginning, e.g., the
** period beginning date for these invoices creates a gap in the
invoicing periods
** for the task (activity_id)
**
************************************************** ***************************************/
ALTER view dbo.viewInvoice_MissingPreviousInvoice
as
select t1.inv_id, t1.inv_activity_id
from tblinvoice_headers as t1
inner join (select inv_activity_id, min(inv_period_beginning) as
inv_period_beginning
from dbo.tblInvoice_Headers
group by inv_activity_id
) as t2 on t2.inv_activity_id = t1.inv_activity_id
where t1.inv_period_beginning <t2.inv_period_beginning
and not exists (select s1.inv_id
from dbo.tblInvoice_Headers as s1
where s1.inv_activity_id = t1.inv_activity_id
and datediff(day, s1.inv_period_ending,
t1.inv_period_beginning) = 1
)