create 90+ days incremental Timepoint variable based on start date 1

[drymnfr]

My table name is tblPurchases and the structure is as follows:
tblPurchasesID	CustomerID	DatePurchased
1				177405    	4/1/2005
2				177405    	7/23/2005
3				177405    	7/30/2005
4				177405    	1/24/2006
5				177405    	6/30/2006
6				177405    	7/24/2006
7				177405    	7/29/2006
8				177405    	2/20/2007
9				177405    	3/30/2007
10				177405    	4/13/2007
11				177405    	7/10/2007
12				177405    	10/2/2007
13				177405    	11/14/2007
14				177405    	12/12/2007
15				220391    	1/19/2005
16				220391    	2/2/2005
17				220391    	2/25/2005
18				220391    	3/18/2005
19				220391    	7/5/2005
20				220391    	6/7/2006
21				220391    	7/11/2006
22				220391    	8/11/2006
23				220391    	9/25/2006
26				220391    	4/2/2007
27				220391    	5/23/2007
28				220391    	8/10/2007
29				220391    	8/24/2007

I would like to be able to create 2 new variables - Timepoint and TimepointComplete as illustrated below:

tblPurchasesID	CustomerID	DatePurchased	Duration	Timepoint	TimepointComplete
1				177405    	4/1/2005		0			1			Y
2				177405    	7/23/2005		113			2			Y
3				177405    	7/30/2005		120			2			Y
4				177405    	1/24/2006		298			4			Y
5				177405    	6/30/2006		455			6			Y
6				177405    	7/24/2006		479			6			Y
7				177405    	7/29/2006		484			6			Y
8				177405    	2/20/2007		690			8			Y
9				177405    	3/30/2007		728			9			Y
10				177405    	4/13/2007		742			9			Y
11				177405    	7/10/2007		830			10			Y
12				177405    	10/2/2007		914			11			N
13				177405    	11/14/2007		957			11			N
14				177405    	12/12/2007		985			11			N
15				220391    	1/19/2005		0			1			Y
16				220391    	2/2/2005		14			1			Y
17				220391    	2/25/2005		37			1			Y
18				220391    	3/18/2005		58			1			Y
19				220391    	7/5/2005		167			2			Y
20				220391    	6/7/2006		504			6			Y
21				220391    	7/11/2006		538			6			Y
22				220391    	8/11/2006		569			7			Y
23				220391    	9/25/2006		614			7			Y
26				220391    	4/2/2007		803			9			Y
27				220391    	5/23/2007		854			10			Y
28				220391    	8/10/2007		933			11			N
29				220391    	8/24/2007		947			11			N

Any purchase made within 90 days of first purchase is considered Timepoint 1.
Any purchase made between 91 and 180 days of first purchase is considered Timepoint 2.
Any purchase made between 91 and 180 days of first purchase is considered Timepoint 2.
Any purchase made between 181 and 270 days of first purchase is considered Timepoint 3.

...and so on for other timepoints

I will use customer 220391 to further explain the logic:

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.

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.

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.

...and so on

The 13th 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.

Any help with this will be greatly appreciated. Thanks in advance.

 

[simian336]

Add one more column to your table, Original Purchase Date OPD. Then update OPD with minimum purchase date for each customer. Then the other columns are just calculations.

Simi

 

[simian336]

That should work nicely...

to get your OPD...

with mytest as
(
select customerid, Min(datepurchased) mindate
from #test
group by customerid
)
update a
set a.opd = b.mindate
from #test a
join mytest b
on a.customerid=b.customerid


Then just fill in your other colums with calculations.

Simi

 

[drymnfr]

Thank you for your help.

As suggested I created the new field MinDate and some other fields which then allowed me to do the rest of the calculations easily, although I must say that I also ended up with a few very long case statements.