Recursive Query

[K2mission]

I've been working with a recursive query recently and I've been unable
to get the expected results. I'm looking to pull a customer purchase
record for each time they purchased a particular product and to give
them a discount if it's been over a year since they purchased a
product and then additional discounts if it's been a year since they
last received a discount. So, if I have the following information:

LineID Customer ProdPurchaseDate
1 Cust1 2007-05-05
2 Cust1 2007-09-20
3 Cust1 2008-04-16
4 Cust1 2008-06-30
5 Cust1 2008-12-20
6 Cust1 2009-07-11

I want to identify lines 1, 4 and 6 as purchase dates that the
customer should have received a discount. Does anyone know how to do
this with a recursive CTE?

Thanks in advance

[John Bell]

Hi

You can use rownumber to define the and a sequence number to the
purchases. If that is used in the CTE then you can join the CTE to
itself in a query to get consequtive purchases. You can also do it my
using identifying consequtive rows that have the minimum purchasedate
greater than the current purchase date.

Please post DDL and example data if you want example code.

John

[--CELKO--]

Do not decide HO(W to do something; specify WHAT you want. Recursion
in not needed and it is expensive. Here is a self-join.

Let's reduce the table to a skeleton:

CREATE TABLE PurchaseDates
(prod_purchase_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL PRIMARY
KEY);

INSERT INTO PurchaseDates
VALUES ('2007-05-05'),
('2007-09-20'),
('2008-04-16'),
('2008-06-30'),
('2008-12-20'),
('2009-07-11');

SELECT * FROM PurchaseDates

/* give them a discount if it's been over a year since they purchased

a product and then additional discounts if it's been a year since they

last received a discount ..

But your data does not support your answer. Clearly '2007-05-05' has
no prior history, so it cannot get a lapsed customer discount. The
follwoing query displays the time in months since the prior purchase.
None of them have been 12+ months apart.
*/

WITH Purchase_History (prod_purchase_date, purchase_seq)
AS
(SELECT prod_purchase_date,
ROW_NUMBER() OVER (ORDER BY prod_purchase_date)
FROM PurchaseDates),

Annual_Discount_History (prod_purchase_date, lapse)
AS
(SELECT H2.prod_purchase_date, DATEDIFF (MM, H1.prod_purchase_date,
H2.prod_purchase_date) AS lapse
FROM Purchase_History AS H1, Purchase_History AS H2
WHERE H1.purchase_seq = H2.purchase_seq-1)

SELECT * FROM Annual_Discount_History
ORDER BY prod_purchase_date;

Prod_purchase_date lapse
===================-=====
2007-09-20 4
2008-04-16 7
2008-06-30 2
2008-12-20 6
2009-07-11 7

[Tony Rogerson]

> INSERT INTO PurchaseDates
> VALUES ('2007-05-05'),
> ('2007-09-20'),

At what point are you actually going to admit that relying on the position
and number of columns in the table schema is an extremely bad practice?

Also the use of that style of date is not consistent in the product SQL
Server for which this forum is for, though for not much longer thankfully!

User should use this instead which follows quality programming practices....

INSERT INTO PurchaseDates ( prod_purchase_date )

> VALUES ('2007-05-05'),
> ('2007-09-20'),

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:60c58be4-d26b-4dab...@f6g2000yqa.googlegroups.com...