determine if date is consecutive by person ID

Posted on 2014-08-12
Last Modified: 2014-08-13

I have a table with a member id and an eligibility date. I need to create a record by member id with the minimum eligibility date and max eligibility date where the dates are consecutive (by month). The logic is also that there can be be a one month gap to be considered "consecutive". When the dates aren't consecutive, it would be a new record.

MemberID        EligibilityDT
XYZ                         1/1/2014
XYZ                         2/1/2014
XYZ                        4/1/2014
XYZ                        7/1/2014

The desired output would be:

MemberID      StartDT       EndDT
XYZ                1/1/2014      4/1/2014
XYZ               7/1/2014        7/1/2014

My logic is cursor processing. I am looping thru by member and comparing a date variable to the next date variable by DateDiff. Then swithcing the value of the date variable vs. the next date.

Am I  even correct that cursor processing is the way to go. It just seems like there has to be an easier way.
The table has 10M rows. Any insight is appreciated.

SQL Server 2012 if that matters.

Thank you.
Question by:jvoconnell
    LVL 47

    Expert Comment

    SQL Server 2012 if that matters = thank you, thank you, thank you - knowing version is always important
    LVL 24

    Accepted Solution

    This query will get you the desired result:
    with dates as(
      select MemberID, 
      row_number() OVER(PARTITION BY MemberID ORDER BY EligibilityDT) RN,
      LAG(EligibilityDT, 1, EligibilityDT) OVER(PARTITION BY MemberID ORDER BY EligibilityDT) PreviousDT,
      MAX(EligibilityDT) OVER(PARTITION BY MemberID) MaxDate
      FROM Table1
    startDates AS(
      SELECT MemberID, EligibilityDT,
      CASE WHEN rn = 1 OR DATEDIFF(m, PreviousDT, EligibilityDT) >2 THEN EligibilityDT END StartDate, 
      FROM dates ),
    startEndDates AS(
      SELECT MemberID, StartDate,
      LEAD(PreviousDT, 1, MaxDate) OVER(PARTITION BY MemberID ORDER BY StartDate) EndDate
      FROM startDates WHERE StartDate IS NOT NULL)
    SELECT  * from startEndDates
    ORDER BY 1,2

    Open in new window

        CREATE TABLE Table1
        	([MemberID] varchar(3), [EligibilityDT] datetime)
        INSERT INTO Table1
        	([MemberID], [EligibilityDT])
        	('XYZ', '2014-01-01 11:00:00'),
        	('XYZ', '2014-02-01 11:00:00'),
        	('XYZ', '2014-04-01 11:00:00'),
        	('XYZ', '2014-07-01 10:00:00'),
        	('YYZ', '2014-01-01 11:00:00'),
        	('YYZ', '2014-05-01 10:00:00'),
        	('YYZ', '2014-06-01 10:00:00'),
        	('YYZ', '2014-08-01 10:00:00')
        | MEMBERID |                      STARTDATE |                        ENDDATE |
        |      XYZ | January, 01 2014 11:00:00+0000 |   April, 01 2014 11:00:00+0000 |
        |      XYZ |    July, 01 2014 10:00:00+0000 |    July, 01 2014 10:00:00+0000 |
        |      YYZ | January, 01 2014 11:00:00+0000 | January, 01 2014 11:00:00+0000 |
        |      YYZ |     May, 01 2014 10:00:00+0000 |  August, 01 2014 10:00:00+0000 |

    Open in new window

    LVL 47

    Expert Comment

    LEAD() LAG() were introduced at SQL 2012 so  it was worth knowing.

    Well done chaau!

    just in case: no points please
    LVL 1

    Author Closing Comment

    Wow! This is great. I new I was attacking this the wrong way. Thank you so much!!  Thank you both for jumping in.
    LVL 24

    Expert Comment

    Thanks for that. I am curious how performant the query is. Have you tried against your 10M records? How fast was it?
    LVL 1

    Author Comment

    Performance is awesome! We decided to go back even further in history. We have 24M rows and the query finished in 22 seconds. My cursor logic was running at @ 3 hours for a small sampling of data. THANK YOU AGAIN!
    LVL 47

    Expert Comment

    Thanks for the feedback.

    Take it as a lesson to use "set based" operations in SQL whenever you can and use cursors only where they are absolutely the only option :)

    Cheers, Paul

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now