Membership Span
-
I have a table called membership which contains memberID, effdat, expdat. When a member's membership expires and does not re-enroll, the membership breaks. After a while, the member may rejoin.
So, here is the membership table:
CREATE TABLE membership
(RecId int IDENTITY NOT NULL PRIMARY KEY,
MemberID varchar(10),
EffDate smalldatetime,
ExpDate smallDatetime)
and here are some sample:
INSERT INTO membership (MemberID, EffDate, ExpDate)
SELECT ('M3', '01/01/2005', '04/30/2005'),
('M3', '05/01/2005', '08/31/2005'),
('M3', '10/01/2005', '12/31/2005'),
('M3', '01/01/2006', '04/30/2006'),
('M3', '01/01/2007', '04/30/2009')
I want to have a history of the member's membership, merging the continous expiration date and effective date.
So, after the merge, the member history should appears as:
MemberID, Effective_Date, Expiration Date
M3 '01/01/2005' '08/31/2005'
M3 '10/01/2005' '04/30/2006'
M3 '01/01/2007' '04/30/2009'
How I can do that
Thanks,
-
The short answer is 'with a recursive CTE.' It's an interesting question, though, and I may work up an answer for you this weekend.
Do you can about weekends and holidays, or do you have to have an effective date the calendar day after your expiration date?
Is it an error if ranges overlap?
Your data is not consistent by the way. In '05/01/2005' should be '01/01/2005' or vice versa.
-
First of all, thank you very much for your reply.
To answer your question, effective date could start at date, regardless weekend or holiday, and it would be fine if there is an overlap between previously expiration date and next effective date, and this should not be regard as an error.
Secondly, in my example, the first membership period is from 01/01/2005 to 04/30/2005, then the member renew his membership from 05/01/2005 to 08/31/2005, then there is a break in his membership and next time he starts from 10/01/2005 to 12/31/2005, then he continue his membership from 01/01/2006 to 04/30/2006, then his member breaks again...
Again thanks for your reply.
Qjlee
-
You don't need a recursive CTE to do this. Here is one way to do it.
;WITH cteMembership AS (SELECT MemberID, EffDate, ExpDate,
ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY EffDate) AS rn
FROM Membership
),
cteStart AS (
SELECT
M1.MemberID,
M1.EffDate,
ROW_NUMBER() OVER (PARTITION BY M1.MemberID ORDER BY M1.rn) AS rn
FROM cteMembership M1
WHERE NOT EXISTS (
SELECT 1 FROM cteMembership M0
WHERE (M1.MemberID = M0.MemberID)
AND (M1.rn = M0.rn + 1)
AND (M1.EffDate <= DATEADD(day, 1, M0.ExpDate)))
),
cteEnd AS (
SELECT
M1.MemberID,
M1.ExpDate,
ROW_NUMBER() OVER (PARTITION BY M1.MemberID ORDER BY M1.rn) AS rn
FROM cteMembership M1
WHERE NOT EXISTS (
SELECT 1 FROM cteMembership M2
WHERE (M2.MemberID = M1.MemberID)
AND (M2.rn = M1.rn + 1)
AND (M2.EffDate <= DATEADD(day, 1, M1.ExpDate)))
)
SELECT
A.MemberID,
A.EffDate AS Effective_Date,
B.ExpDate AS Expiration_Date
FROM cteStart AS A INNER JOIN cteEnd AS B
ON (A.MemberID = B.MemberID AND A.rn = B.rn)
The cteStart CTE retrieves the rows where there is a break in membership immediately beforehand, or where the row is the first for that MemberID, and therefore provides the Effective_Date. The cteEnd CTE retrieves the rows where there is a break in membership immediately afterwards, or where the row is the last for that MemberID, and there provides the Expiration_Date. There should always be the same number of rows in cteStart and cteEnd, so we can simply join each row in the two CTEs on MemberID and ordinal position to get the required result.
-
Actually, check out today's article...
http://qa.sqlservercentral.com/articles/CTE/71169/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Not very efficient, but should give the correct results
SELECT s1.MemberID,s1.EffDate,
MIN(t1.ExpDate) AS ExpDate
FROM membership s1
INNER JOIN membership t1 ON t1.MemberID=s1.MemberID
AND s1.EffDate <= t1.ExpDate
AND NOT EXISTS(SELECT * FROM membership t2
WHERE t2.MemberID=t1.MemberID
AND (t1.ExpDate+1) >= t2.EffDate AND t1.ExpDate < t2.ExpDate)
WHERE NOT EXISTS(SELECT * FROM membership s2
WHERE s2.MemberID=s1.MemberID
AND s1.EffDate > s2.EffDate AND (s1.EffDate-1) <= s2.ExpDate)
GROUP BY s1.MemberID,s1.EffDate
ORDER BY s1.MemberID,s1.EffDate;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
See Itzik Ben-Gan's blog for another alternative, we used this to create our own modified membership table for reporting:
http://www.sqlmag.com/blogs/puzzled-by-t-sql
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply