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.

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:
http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp

Razvan

It won't produce results in the format that you wan't, but this will show you
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

Show quoteHide quote

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: http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp

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

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

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.developersdex.com ***

Thanks Joe!
Your books are great!

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:
http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp

Razvan

Thanks so much for you assistance.  This was of immense help to me.

identify 'next' pk value
Help with relational set division, please! Good challange!
OPTIMIZER LOCK HINTS
Select most recent date time
What Are The Issues With MS Access Client to SQL Server DB