Check the continuity of dates

I am trying to find coverage dates for a given list of dates that may
have breakage.
For example person 123 has the below records:
PersonID Date
123 10/15/03
123 11/15/03
123 12/15/03
123 3/15/05
123 4/15/05
123 5/15/05
For PersonID 123 I would expect 2 records with coverage dates of
10/1/03 - 12/31/03 and another one of 3/1/05 - 5/31/05. What is the
best approach for this? I am unconcerned about getting the First/End
of the month dates because I have functions to get this info. I need
to know the best way to check the continuity of dates.It won't produce results in the format that you wan't, but this will show yo
u
if there are any gaps in coverage:
select t1.PersonID, DATEADD(mm, 1, t1.[Date]) FROM [YourTable] t1
WHERE NOT EXISTS
(SELECT t2.[Date] FROM [YourTable] t2 WHERE t1.PersonID=t2.PersonID
AND t2.[Date] = DATEADD(mm, 1, t1.[Date])
This will show the months for which a lapse in coverage begins.
Assuming that there was only 1 gap in coverage for all customers, you could
do something like this:
select t1.PersonID, DATEADD(mm, 1, t1.[Date]) AS "LapseBegins",
t5.LapseEnds
FROM [YourTable] t1
WHERE NOT EXISTS
(SELECT t2.[Date] FROM [YourTable] t2 WHERE t1.PersonID=t2.PersonID
AND t2.[Date] = DATEADD(mm, 1, t1.[Date])
INNER JOIN
(
select t3.PersonID, t3.[Date] AS "LapseEnds" FROM [YourTable] t3
WHERE NOT EXISTS
(SELECT t4.[Date] FROM [YourTable] t4 WHERE t3.PersonID=t4.PersonID
AND t3.[Date] = DATEADD(mm, -1, t4.[Date])
) t5
ON t5.PersonID=t5.PersonID
This would return results like
PersonID LapseBegins LapseEnds
-- -- --
123 1/15//04 3/15/05
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"cxg" wrote:

> I am trying to find coverage dates for a given list of dates that may
> have breakage.
> For example person 123 has the below records:
> PersonID Date
> 123 10/15/03
> 123 11/15/03
> 123 12/15/03
> 123 3/15/05
> 123 4/15/05
> 123 5/15/05
> For PersonID 123 I would expect 2 records with coverage dates of
> 10/1/03 - 12/31/03 and another one of 3/1/05 - 5/31/05. What is the
> best approach for this? I am unconcerned about getting the First/End
> of the month dates because I have functions to get this info. I need
> to know the best way to check the continuity of dates.
>|||Considering the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose that we expect the following result:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns the above result:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
The query can be simplified using a CTE in SQL Server 2005 (or a view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
The first query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||Just a little addition:
I understand that your requirements are a little different: you don't want
to check for consecutive days, but for dates in consecutive months. I will
leave the modifications for you, as an exercise :)
Razvan|||Google the use of an auxilary Calendar table. DATE is both a reserved
word and too vague to be data element name. And the only format allowed
in Standard SQL is ISO-8601.
We need a range of dates to consider for the report, so make them
parameters. We want to find calendar dates in the range that are not
matched to foo_dates in the same range.
SELECT F1.person_id, C1.cal_date,
@.report_start_date, @.report_end_date
FROM Foobar AS F1, Calendar AS C1
WHERE NOT EXISTS
(SELECT *
FROM Foobar AS F2
WHERE C1.cal_date BETWEEN @.report_start_date AND
@.report_end_date
AND F1.cal_date BETWEEN @.report_start_date AND
@.report_end_date
AND C1.cal_date = F1.foo_date);
If you want to express this result as ranges, you can Google some other
postings about gaps shown as (start, end) pairs.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Consider the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose the expected result is:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns the above results:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
We can simplify it a bit using a CTE in SQL Server 2005 (or a view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
I understand that your requirements are different: you do not need to
have consecutive days, but dates in consecutive months. I will leave
the modifications of the query to you, as an exercise.
The above query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||A small update:
I understand that your requirements are a little different: you don't
want to check for consecutive days, but for dates in consecutive
months. I will leave the modifications for you, as an exercise :)
Razvan|||Consider the following DDL and sample data:
CREATE TABLE TheTable (
PersonID int,
SomeDate smalldatetime,
PRIMARY KEY (PersonID, SomeDate)
)
INSERT INTO TheTable VALUES (123, '20031010')
INSERT INTO TheTable VALUES (123, '20031011')
INSERT INTO TheTable VALUES (123, '20031012')
INSERT INTO TheTable VALUES (123, '20050503')
INSERT INTO TheTable VALUES (123, '20050504')
INSERT INTO TheTable VALUES (123, '20050505')
INSERT INTO TheTable VALUES (124, '20050503')
INSERT INTO TheTable VALUES (124, '20050504')
INSERT INTO TheTable VALUES (124, '20050505')
INSERT INTO TheTable VALUES (124, '20050606')
INSERT INTO TheTable VALUES (125, '20050707')
Let's suppose the expected result is:
PersonID StartDate EndDate
-- -- --
123 2003-10-10 2003-10-12
123 2005-05-03 2005-05-05
124 2005-05-03 2005-05-05
124 2005-06-06 2005-06-06
125 2005-07-07 2005-07-07
The following query returns this result:
SELECT PersonID, SomeDate AS StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=A.PersonID
AND C.SomeDate>=A.SomeDate
AND C.SomeDate<ISNULL((
SELECT MIN(D.SomeDate) FROM TheTable D
WHERE D.PersonID=C.PersonID
AND D.SomeDate>A.SomeDate
AND NOT EXISTS (
SELECT * FROM TheTable E
WHERE D.SomeDate=E.SomeDate+1
)
),C.SomeDate+1)
) AS EndDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
The query can be simplified by using a CTE in SQL Server 2005 (or a
view):
WITH MyCTE AS (
SELECT PersonID, SomeDate AS StartDate
FROM TheTable A
WHERE NOT EXISTS (
SELECT * FROM TheTable B
WHERE A.PersonID=B.PersonID
AND A.SomeDate=B.SomeDate+1
)
)
SELECT PersonID, StartDate, (
SELECT MAX(C.SomeDate) FROM TheTable C
WHERE C.PersonID=X.PersonID
AND C.SomeDate>=X.StartDate
AND C.SomeDate<ISNULL((
SELECT MIN(Y.StartDate) FROM MyCTE Y
WHERE Y.PersonID=C.PersonID
AND Y.StartDate>X.StartDate
),C.SomeDate+1)
) as EndDate
FROM MyCTE X
I understand that your requirements are a little different: you don't
want to check for consecutive days, but for dates in consecutive
months. I will leave the modifications to you, as an exercise. :)
The above query was inspired by reading (a few years ago) the following
article:
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp[
/url]
Razvan|||Thanks so much for you assistance. This was of immense help to me.|||Thanks Joe!
Your books are great!