October 16, 2012 at 8:18 am
Hi Guys,
I need to create a script that can calculate the number of continues membership years. I can do this in a cursor but this will take forever to run anyone else got any bright ideas?
Maybe a CTE?
Scenario
Each Member can have numerous membership years eg
Member 1 Has 4 Continuous Years
StartDate 01/01/12 - EndDate 31/12/12 Year 4
StartDate 01/01/11 - EndDate 31/12/11 Year 3
StartDate 01/01/10 - EndDate 31/12/10 Year 2
StartDate 01/01/09 - EndDate 31/12/09 Year 1
StartDate 01/01/07 - EndDate 31/12/07 Year 1
StartDate 01/01/02 - EndDate 31/12/02 Year 1
I need to work the number of continuous years (continuous means a max gape of 365 days from previous membership end date)
Any ideas appreciated.
Thanks,
October 16, 2012 at 8:27 am
October 16, 2012 at 9:51 am
Based on the two sources mentioned in the prior post, here's a possible approach. First, create the data. I've added a second member based on the structure of the data you presented.
Create table MemberDate
(MemberID int, StartDate datetime, EndDate DateTime)
Insert into MemberDate
Select 1, '1/1/12', '12/31/12'
Union
Select 1, '1/1/11', '12/31/11'
Union
Select 1, '1/1/10', '12/31/10'
Union
Select 1, '1/1/09', '12/31/09'
Union
Select 1, '1/1/07', '12/31/07'
Union
Select 1, '1/1/02', '12/31/02'
Union
Select 2, '1/1/03', '12/31/03'
Union
Select 2, '1/1/04', '12/31/04'
Union
Select 2, '1/1/06', '12/31/06'
;
Now the query. I've broken out several CTEs to make the steps more clear. This code can be condensed--I tend to be a bit verbose in my TSQL. :hehe:
With cteYears
As
(
Select
MemberID,
YearValue = Cast(DatePart(yyyy,EndDate) as int),
DateGroupRowNum = Row_Number() over (order by Cast(DatePart(yyyy,EndDate) as int))
From
MemberDate
Group by MemberID, EndDate
),
cteDiff
As
(
Select
MemberID,
YearValue,
Diff = YearValue - DateGroupRowNum
From
cteYears
),
cteYearGroup
As
(
Select
MemberID,
Min(YearValue) as start_range,
Max(YearValue) as end_range,
Years = Max(YearValue) - Min(YearValue) + 1
From
cteDiff
Group by MemberID, Diff
)
Select
MemberID,
Max(Years) as MemberYears
From
cteYearGroup
Group by MemberID
;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply