Episodes and Timepoints - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Episodes and Timepoints

Author

Topic

drymnfr
Starting Member

9 Posts

Posted - 2010-05-20 : 20:08:16

I have the following table structure:


CustomerID	PurchaseDate
220391	1/19/2005
220391	2/2/2005
220391	2/25/2005
220391	3/18/2005
220391	7/5/2005
220391	6/7/2006
220391	7/11/2006
220391	8/11/2006
220391	9/25/2006
220391	2/3/2007
220391	2/28/2007
220391	4/2/2007
220391	5/23/2007
220391	8/10/2007
220391	8/24/2007

I would like to be able to do the following:

1) create Field - Episode
A gap of 180 or more days in between consecutive purchase dates is considered the start of a new episode.

2) create fields - Timepoint and TimepointComplete
Any purchase made within 90 days of first purchase is considered Timepoint 1. If we approach transactions more than 90 days, then timepoint 1 is considered complete and records marked 'Y'..
Any purchase made between 91 and 180 days of first purchase is considered Timepoint 2. If we approach transactions more than 180 days, then timepoint 2 is considered complete and records marked 'Y'.
Any purchase made between 91 and 180 days of first purchase is considered Timepoint 2. If we approach transactions more than 180 days, then timepoint 2 is considered complete and records marked 'Y'.
Any purchase made between 181 and 270 days of first purchase is considered Timepoint 3. If we approach transactions more than 270 days, then timepoint 3 is considered complete and records marked 'Y'.

...and so on for other timepoints

so that I get the following table:


CustomerID	PurchaseDate	Episode	Timepoint	TimepointComplete
177405	4/1/2005		1		1		Y
177405	7/23/2005		1		2		Y
177405	7/30/2005		1		2		Y
177405	1/24/2006		1		4		Y
177405	6/30/2006		1		6		Y
177405	7/24/2006		1		6		Y
177405	7/29/2006		1		6		Y
177405	2/20/2007		2		8		Y
177405	3/30/2007		2		9		Y
177405	4/13/2007		2		9		Y
177405	7/10/2007		2		10		Y
177405	10/2/2007		2		11		N
177405	11/14/2007		2		11		N
177405	12/12/2007		2		11		N
220391	1/19/2005		1		1		Y
220391	2/2/2005		1		1		Y
220391	2/25/2005		1		1		Y
220391	3/18/2005		1		1		Y
220391	7/5/2005		1		2		Y
220391	6/7/2006		2		6		Y
220391	7/11/2006		2		6		Y
220391	8/11/2006		2		7		Y
220391	9/25/2006		2		7		Y
220391	2/3/2007		2		9		Y
220391	2/28/2007		2		9		Y
220391	4/2/2007		2		9		Y
220391	5/23/2007		2		10		Y
220391	8/10/2007		2		11		N
220391	8/24/2007		2		11		N

Thanks in advance for any help with this.

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-05-21 : 05:54:50

Hi
Please Find below the code

/*
--STEP 1
Select
(Select Top 1 PurchaseDate From Test Order By PurchaseDate),
* From Test Order By PurchaseDate
--STEP 2
Create View Test_Cons as
Select (Select Top 1 PurchaseDate From Test Order By PurchaseDate) AS StartDate ,
CustomerId , PurchaseDate From Test

-- STEP 3
Select
DATEDIFF(day, StartDate , PurchaseDate) AS NoOfDays,
CASE WHEN DATEDIFF(day, StartDate , PurchaseDate) > 180 THEN
2
ELSE
1
END
as Episode, StartDate , CustomerId , PurchaseDate ,
ROW_NUMBER() OVER (ORDER BY CASE WHEN DATEDIFF(day, StartDate , PurchaseDate) >180 THEN
181
ELSE
180
END ASC) AS RowNum

from Test_Cons
ORDER BY PurchaseDate

*/

I have not included for Timepoint TimepointComplete
Let me know the logic in detailed so that i can give a solution.

Regards
Naveen Gopinath

Lets unLearn

drymnfr
Starting Member

9 Posts

Posted - 2010-05-21 : 13:00:03

Thank you for your response.

To my original post above, I have made corrections to some mistakes I had made earlier(with Timepoints). I have added another customer, to further illustrate. Also, important to note that some customers may have made multiple purchases on the same date.

I'm sorry, I know this one is a really convoluted, with the timepoints etc.,

To continue with the logic, I will use customer 220391 below:

The FIRST PURCHASE was made on 1/19/2005. We will mark that Timepoint 1
The 2nd purchase was made on 2/2/2005, which is 14 days from FIRST PURCHASE. Since this falls within 90 days of first purchase, we will mark this Timepoint 1.
The 3rd purchase was made on 2/25/2005, which is 37 days from FIRST PURCHASE. Since this falls within 90 days of first purchase, we will mark this also Timepoint 1.
The 4th purchase was made on 3/18/2005, which is 58 days from FIRST PURCHASE. Since this falls within 90 days of first purchase, we will mark this also Timepoint 1.

The 5th purchase was made on 7/5/2005, which is 167 days from FIRST PURCHASE. Since this falls between 91 days and 180 days of first purchase, we will mark this Timepoint 2. As we have now moved on to Timepoint 2, we will mark all Timepoint 1 purchases as complete(TimepointComplete=Y)

There are no purchases made for the following Timepoints
3 (181 thru 270 days)
4 (271 thru 360 days)
5 (361 thru 450 days)

The 6th purchase was made on 6/7/2006, which is 504 days from FIRST PURCHASE. Since this falls between 451 days and 540 days of first purchase, we will mark this Timepoint 6. As we have now moved on to Timepoint 6, we will mark all Timepoint 2 purchases as complete(TimepointComplete=Y)

The 7th purchase was made on 7/11/2006, which is 538 days from FIRST PURCHASE. Since this falls between 451 days and 540 days of first purchase, we will mark this Timepoint 6.

The 8th purchase was made on 8/11/2006, which is 569 days from FIRST PURCHASE. Since this falls between 541 days and 630 days of first purchase, we will mark this Timepoint 7. As we have now moved on to Timepoint 7, we will mark all Timepoint 6 purchases as complete(TimepointComplete=Y)

...and so on

The 15th purchase was made on 8/24/2007, which is 947 days from FIRST PURCHASE. Since this falls between 901 days and 990 days of first purchase, This falls under Timepoint 11. all previous Timepoints will be marked complete. Since this is the last purchase and the purchase date is nowhere close to 990 days, this timepoint will not be marked complete.

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-07 : 03:25:40

Have you got the solution or still you have any issue.
if so let me know the issue.

Lets unLearn

drymnfr
Starting Member

9 Posts

Posted - 2010-06-10 : 20:40:24

Thanks for following up.

Yes, I did come up with a solution. I had asked the same question on another forum and as suggested, I created new fields, such as Min(PurchaseDate), Timepoint_StartDate, Timepoint_EndDate, followed by a whole bunch of CASE WHEN's, etc.,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources