Contiguous Dates

Question

Here is the table that I am working with:

MemberID     MembershipStartDate           MembershipEndDate
=================================================================
123          2010-01-01 00:00:00.000      2012-12-31 00:00:00.000
123          2011-01-01 00:00:00.000      2012-12-31 00:00:00.000
123          2013-05-01 00:00:00.000      2013-12-31 00:00:00.000
123          2014-01-01 00:00:00.000      2014-12-31 00:00:00.000
123          2015-01-01 00:00:00.000      2015-03-31 00:00:00.000

What I want is to create one row that shows continuous membership, and a second row if the membership breaks by more than 2 days, with a new start and end date..

So the output I am looking for is like:

MemberID     MembershipStartDate          MembershipEndDate
=================================================================
123          2010-01-01 00:00:00.000     2012-12-31 00:00:00.000
123          2013-05-01 00:00:00.000     2015-03-31 00:00:00.000

There is a memberID field attached to these dates which is how they are grouped.

Without the memberID field in the sample data, there's no way for us to know why you chose to combine the records the way you did. — Rabbit, Dec 03 '15 at 17:21
How does `123` have overlapping start/end dates? Ex: `2013/01/01-2013/12/31` & `2013/05/01 - 2013/12/31` — levelonehuman, Dec 03 '15 at 17:39
Yeah, I don't understand the results either. Why are there 2 rows when there's no gap in the different overlapping start and end dates? There should only be one row. — Rabbit, Dec 03 '15 at 17:46
Sorry about that, lots of typo's on my part.. hope it makes more sense now.. thanks for pointing that out — Herman, Dec 03 '15 at 17:48
This is actually a somewhat more complicated problem than it might appear at first glance. Take a look at some of the answers on this question which is possibly a duplicate: http://stackoverflow.com/questions/29549117/how-to-make-calculation-on-time-intervals — Brian Pressler, Dec 03 '15 at 19:45
If anyone gets a chance please upvote so we can get more eyes on this please.. I looked at some similar problems, but they don't exactly fit my situation. — Herman, Dec 04 '15 at 18:02

score 2 · Accepted Answer · answered Dec 04 '15 at 18:38

I've had to deal with this kind of thing before I use something like this

USE tempdb
--Create test Data
DECLARE @Membership TABLE (MemberID int ,MembershipStartDate date,MembershipEndDate date)
INSERT @Membership
(MemberID,MembershipStartDate,MembershipEndDate)
VALUES (123,'2010-01-01','2012-12-31'),
       (123,'2011-01-01','2012-12-31'),
       (123,'2013-05-01','2013-12-31'),
       (123,'2014-01-01','2014-12-31'),
       (123,'2015-01-01','2015-03-31')


--Create a table to hold all the dates that might be turning points
DECLARE @SignificantDates Table(MemberID int, SignificantDate date, IsMember bit DEFAULT 0)

--Populate table with the start and end dates as well as the days just before and just after each period
INSERT @SignificantDates (MemberID ,SignificantDate)
SELECT MemberID, MembershipStartDate FROM @Membership
UNION 
SELECT MemberID,DATEADD(day,-1,MembershipStartDate ) FROM @Membership
UNION 
SELECT MemberID,MembershipEndDate FROM @Membership
UNION 
SELECT MemberID,DATEADD(day,1,MembershipEndDate) FROM @Membership

--Set the is member flag for each date that is covered by a membership
UPDATE sd SET IsMember = 1
FROM @SignificantDates  sd
JOIN @Membership m ON MembershipStartDate<= SignificantDate AND SignificantDate <= MembershipEndDate

--To demonstrate what we're about to do, Select all the dates and show the IsMember Flag and the previous value
SELECT sd.MemberID, sd.SignificantDate,sd.IsMember, prv.prevIsMember 
FROM
@SignificantDates sd 
JOIN (SELECT 
      MemberId,
      SignificantDate,
      IsMember, 
      Lag(IsMember,1) OVER (PARTITION BY MemberId ORDER BY SignificantDate desc) AS prevIsMember FROM @SignificantDates 
      ) as prv
ON sd.MemberID = prv.MemberID
AND sd.SignificantDate = prv.SignificantDate
ORDER BY sd.MemberID, sd.SignificantDate

--Delete the ones where the flag is the same as the previous value
delete sd
FROM
@SignificantDates sd 
JOIN (SELECT MemberId, SignificantDate,IsMember, Lag(IsMember,1) OVER (PARTITION BY MemberId ORDER BY SignificantDate) AS prevIsMember  FROM @SignificantDates ) as prv
ON sd.MemberID = prv.MemberID
AND sd.SignificantDate = prv.SignificantDate
AND prv.IsMember = prv.prevIsMember 


--SELECT the Start date for each period of membership and the day before the following period of non membership
SELECT 
nxt.MemberId,
nxt.SignificantDate AS MembershipStartDate,
DATEADD(day,-1,nxt.NextSignificantDate)  AS MembershipEndDate
FROM 
(
SELECT 
MemberID,
SignificantDate,
LEAd(SignificantDate,1) OVER (PARTITION BY MemberId ORDER BY SignificantDate)   AS NextSignificantDate,
IsMember
FROM @SignificantDates 
) nxt
WHERE nxt.IsMember = 1

This is brilliant and very easy to follow along.. thank you so much for your help! — Herman, Dec 04 '15 at 18:59
For future people in my situation, to the `JOIN @Membership m ON MembershipStartDate<= SignificantDate AND SignificantDate <= MembershipEndDate` add `AND m.MemberID = sd.MemberID`. Also, this is easily modifiable to work at the time level, just changing day for seconds and the dates for datetimes. As Herman said, this answer is brilliant, I couldn't have figured this out in a lifetime. — Oscar Vasquez, Dec 16 '15 at 02:46

Contiguous Dates

1 Answers1

Linked