February 28, 2008 at 10:12 am
I have a Members table that looks something like this:
[font="Courier New"]
MemberID...... ProductLine.... StartDate..... EndDate
1000.......... Dogs........... 01/01/2001.... 06/03/2001
1000.......... Dogs........... 09/15/2002.... 11/30/2003
1000.......... Cats........... 04/20/2001.... 06/30/2001
1000.......... Dogs........... 01/31/2004.... 01/31/2005
1000.......... Cats........... 02/01/2004.... 06/30/2006
1000.......... Dogs........... 04/01/2006.... 05/01/2006
1000.......... Dogs........... 05/01/2007.... Null
9999.......... Dogs........... 08/15/1999.... 08/30/2000
9999.......... Dogs........... 10/01/2006.... 11/30/2006
9999.......... Dogs........... 01/31/2007.... 01/31/2008
[/font]
I need to analyze the membership "churn", such that I can identify -- by MemberID, ProductLine, and conceivably other groupings -- how frequently a Member drops and then re-adds a membership, and the duration of the drop.
In the above table, I might want to see the average number of drops for ProductLine 'Dogs', and the average duration of those drops. Along those same lines, I would want to be able to filter to see a list of members who have had more than three drops, or members who have had drops exceeding four years in length.
I've done a goodly amount of searching on this site and the web as a whole, but as yet haven't found a good solution... or if I did, I didn't recognize it as such. Any help would be greatly appreciated. Thank you!
JP
February 28, 2008 at 4:26 pm
A kind soul on another forum provided a solution. I have yet to apply it, but it looks correct at first blush. Thought I'd share it here just in case a lurker/searcher (such as myself) comes along later to seek an solution to a similar problem.
--------------------------
--------------------------
Using common table expressions, the row_number() function and the OVER
clause lets you do all of this, for example
drops by memberid and productline would be
; With cte As
(Select MemberID,
ProductLine,
StartDate,
EndDate,
Row_Number() Over(Partition By MemberID, ProductLine Order By StartDate) As
rn
From @TestTable)
Select c.MemberID,
c.ProductLine,
Count(*) As NbrTimes,
Avg(DateDiff (d, c.EndDate, c1.StartDate)) As AvgDaysGone
From cte c
Inner Join cte c1 On c.MemberID = c1.MemberID
And c.ProductLine = c1.ProductLine
And c.rn + 1 = c1.rn
Group By c.MemberID, c.ProductLine
To get the members who have had more than 3 drops would be
; With cte As
(Select MemberID,
ProductLine,
StartDate,
EndDate,
Row_Number() Over(Partition By MemberID, ProductLine Order By StartDate) As
rn
From @TestTable)
Select c.MemberID,
Count(*) As NbrTimes
From cte c
Inner Join cte c1 On c.MemberID = c1.MemberID
And c.rn + 1 = c1.rn
Group By c.MemberID, c.ProductLine
Having Count(*) > 3
And to get members who have had at least one drop of more than four years
; With cte As
(Select MemberID,
ProductLine,
StartDate,
EndDate,
Row_Number() Over(Partition By MemberID, ProductLine Order By StartDate) As
rn
From @TestTable),
GoneFourYears As
(Select c.MemberID
From cte c
Inner Join cte c1 On c.MemberID = c1.MemberID
And c.ProductLine = c1.ProductLine
And c.rn + 1 = c1.rn
Where DateAdd(year, 4, c.EndDate) < c1.StartDate
Group By c.MemberID)
Select c.MemberID,
c.ProductLine,
Count(*) As NbrTimes,
Avg(DateDiff (d, c.EndDate, c1.StartDate)) As AvgDaysGone
From cte c
Inner Join cte c1 On c.MemberID = c1.MemberID
And c.ProductLine = c1.ProductLine
And c.rn + 1 = c1.rn
Where c.MemberID In (Select g.MemberID From GoneFourYears g)
Group By c.MemberID, c.ProductLine
February 28, 2008 at 5:21 pm
Thanks, JP...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply