#### Recursive Query

```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?

```
 0
K2mission
9/7/2010 9:54:25 PM
sqlserver.programming 1873 articles. 0 followers.

3 Replies
1100 Views

Similar Articles

[PageSpeed] 42

```On Tue, 7 Sep 2010 14:54:25 -0700 (PDT), K2mission
<K2mission@hotmail.com> wrote:

>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?
>

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
```
 0
John
9/8/2010 6:46:22 AM
```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

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

```
 0
CELKO
9/8/2010 4:09:12 PM
```> 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--

> 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 ..
>
> 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
>
>
>
>
```
 0
Tony
9/8/2010 5:30:26 PM

Similar Artilces:

2007 queries
In office we are now having xl 2007. how do I create a personal workbook.? I had a macro assigned to the menu bar how do it do it. I had a "proper" macro pasted using VBA window and it was available for the workbook now I cant paste it in the VBA window. what is the new procedure? AFD Hi AFD See http://www.rondebruin.nl/personal.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "afdmello" <afdmello@hotmail.com> wrote in message news:%23dDhk6dCIHA.1168@TK2MSFTNGP02.phx.gbl... > In office we are now having xl 2007. > > how do I create...

Experian query failing
For the past week or so whenever I try to update my credit score in the Credit Center via the free Experian account I opened when I bought M04 Small Business it fails. I know Experian had some problems on their site about a week ago, but it seems to be up now. Anyone else having this problem or know the cause or solution? - J. I am also having that problem. >-----Original Message----- >For the past week or so whenever I try to update my >credit score in the Credit Center via the free Experian >account I opened when I bought M04 Small Business it >fails. I know Exper...

Query re: Incoming messages
I want to setup a filter on my exchange 5.5 server so that incoming email from once particular external source can be re-directed to my mailbox, regardless who it is addressed to. Is this possible?? I do not believe this is possible, with Exchange natively...you would need some sort of third party application to implement this... "Ben Hymas" <ben.hymas@rochford.gov.uk> wrote in message news:cd1001c3ef2c\$d02aa100\$a301280a@phx.gbl... > I want to setup a filter on my exchange 5.5 server so that > incoming email from once particular external source can be > re-direct...

How to Query 11-21-07
Dear all, In SQL Server, I can use Query Analyzer to run SQL query and to debug the query. In acess, there seems no function like Query Analyzer. If I want to run and test a SQL, how can I do that in Access.? Thanks. 1. Go to the Database Window 2. Go to the Queries tab 3. Click on New Query 4. Click Design View 5. Don't select a table and click Cancel 6. Click the SQL view button 7. Type or paste the SQL into the window 8. Click the Datasheet View tool button to view the results of the query. -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at htt...

Getting result of a query in a variable
Hello, I'm quite new to VBA and Access, but I want to get the result of a select statement into a variable to be able to work with it. The select statement will return only one value. Can anyone help me on this? Use DLookup() to get the field from the query. Explanation of how it works: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html The article refers to a table, but you can put your query name where it expects the name of the table. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tip...

Printing Query
Is there a way of printing an area which printed landscape would spread over 2 pages as 1 page (not by fitting to page) but by printing what would appear on page 2 below that on page 1 as the data is not deep (area to be printed is P5..AK16) ...

Query parameter help
IIf(IsNull([Forms]![C25]![StartDateFilter]),[Date1],[Forms]![C25]![StartDateFilter]) The above parameter works. If there is a date, it filters with that date, if not, no filtering is done. I would like it to restrict by date greater than or equal to [Forms]![C25]![StartDateFilter]) I have tried several ways to do this to no avail. Here are two examples. (1) IIf(IsNull([Forms]![C25]![StartDateFilter]),[Date1],>=[Forms]![C25]![StartDateFilter]) (2) IIf(IsNull([Forms]![C25]![StartDateFilter]),[Date1],>=([Forms]![C25]![StartDateFilter])) Any suggestions? Hopefully this is helpful... ...

query to join multiple queries
Need help with this, This is a query that I'm using to consolidate other queries that perform the same calculations, only for different date periods. SELECT [1stPeriod].FltNumOrgDst, [1stPeriod].ExpLF, [2ndPeriod].ExpLF FROM ((((1stPeriod INNER JOIN 2ndPeriod ON [1stPeriod].FltNumOrgDst = [2ndPeriod].FltNumOrgDst GROUP BY [1stPeriod].FltNumOrgDst, [1stPeriod].ExpLF, [2ndPeriod].ExpLF; The problem I have with it, is that if a FltNumOrgDst doesn't appear in all the other queries, it eliminates from the consolidating query. It doesn't return blank or null values I think. I nee...

Form/Query Question
In Access 2007, I have a table that has the following columns: First Name, Last Name, Date of Birth, Hair Color, Height, Weight, and Current Location. I want to create a form that I can go into and find all of the individuals that were born after 1980 and with brown hair; or possibly all the individuals with brown hair that weigh under 180. Basically I want to be able to search the table based on inserting the specific parameters I want into a form. And then I want to be able to see a report of all the individuals that meet my parameters to print out. How can I go about this? Y...

total query with last date
I have a table with 5 fields ID PersonID Date Amount and Type I want a query with the last record of each person based on date and I want the results like this PersonID LastOfDate Amount Type How can i do that thanks On Jun 19, 8:49 pm, zions...@gmail.com wrote: > I have a table with 5 fields > ID PersonID Date Amount and Type > > I want a query with the last record of each person based on date and > I > want the results like this > > PersonID LastOfDate Amount Type > > How can i do that > thanks You need 2 queries to accomplish this. First make a G...

Get next ACTINDX with queries
Can anyone tell me how can I get the next ACTINDX by SQL code so I can create accounts directly through database queries? I need this for an application interface. Already tried eConnect object taUpdateCreateAccountRcd and it worked, but as it is a large amount of accounts it takes too much time, which causes other problems in my implementation. Any help will be greatly appreciated. Regards, Israel Hernandez Try this. select MAX(ACTINDX)+1 from GL00100 "ISRAELHERNANDEZ" wrote: > > Can anyone tell me how can I get the next ACTINDX by SQL code so I can > crea...

Question on Sorting within a query
Hello, I have 1 query which is the basis for a datasheet and a report. It seems that no matter what I specify in the ORDER BY clause, the sort order differs in the report and datasheet. Same thing results if I ORDER BY more than 1 column. It also seems that if I specify sorting by more than 1 column in design view, sorting only occurs on the first column. When I switch to SQL view, both columns do show up in the ORDER BY clause, but still only 1 column is sorting. Is there a bug of some sort that pops up when sorting query results? TIA, Rich A report completely ignores the OrderB...

Compare queries
Hi all I have several access programs that are extremely similar but written for a purpose at the time I now wish to combine into one access program Is there a simple way of checking queries between these programs to see if they do the same job or not Hope this makes sense, regards, Garry ...

Opening a query from a command button
I have several queries opening from a single command button. The first should be: DoCmd.OpenQuery DELETEtbldatentry I get an error message telling me "DELETEtbldatentry = empty" the query name is correct (including case) and the query opens and runs fine when opened manually. What am I doing wrong? Paul Please provide a bit more context in which this line of code is being executed... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a produc...

Enable automatic query refresh for all workbooks?
I know there's a way (according to a KB article on Office 2000 SR1), and in Office XP I must have turned this off somehow, since my workbook didn't ask me everytime if it should refresh or not, but I can't seem to find where this checkbox is in Office 2003! This checkbox has been removed in XL2003 for security reasons. You cannot off the prompt using the XL interface. Modifying the following registry key will give you the same effect: Path: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options Key: QuerySecurity Values: 0 = Prompt on all workbooks 1 = Do Not Prom...

Query for the outstanding balance
Dear all, I just notice that I posted the following message to the wrong forum, I have posted to Database Design by accident, I should post in Query section: My question is : I have designed a database with a Receivable Table and a Payment Table to keep track on the rental income and receivable. The Receivable Table is with the following fields AutoField PeriodID RentalID Amount Payment Table has the following fields AutoField PeriodID RentalID AmountPaid I have created a monthly job to create receivable per rental agreement (we have tenancy agreement without expiry, ther...

How to set the parameter query for this case?
Dear all, I have 2 fields, Eng and Math. I would like to set the parameter query for Eng and Math ==> Eng min, Eng max, Math min and Math max. The Eng query criteria can be "Between Eng min AND Eng max" and the Math query criteria can be "Between Math min AND Math max". But...if I would it can be allowed to have "Null entry", how can I set the Eng and Math query criteria? Please kindly help me, thanks a lot!! All combinations will be like... Eng : Between Eng min AND Eng max, Between Null AND Eng max, Between Eng min AND Null, Between Null AND Null...

LDAP query for hidden mailboxes
Hi I have Exchange 5.5 server . When I try to do a LDAP query for hidden receipients or public folders which are hidden, I do not get a responce. Can anyone tell me how to enable this on Exchange without changing the hidden attribute. I would really appreciate it. Shekhar ...

entering parameter criteria MS Query
Hope someone can help. I’m trying to extract data from a SQL Server 2005 into Excel, using a parameter criteria in MS Query. If I use criteria in a field with an integer data type it works fine. If I use a field with an NVARCHAR data type (which is the one I want) I get a “Syntax Error” message. There seems to be no way of entering criteria into this type of field which MS Query will accept. If I enter james it defaults to N’james‘, which works as syntax criteria in SQL Server Management Studio 2005, but not in MS Query. What am I doing wrong?! ...

In my query I'm generating totals by groups (Routers) I have 4 groups the field name is router, Sample Qty is one of my field and groups (Routers) is the other field, my query returns the total by group/router, This is what I get as an answer This is what I want Field Field SumOfQty Router(Group) SumOfQty Router(Group) 50 Loose Cases 150 Loose Cases 75 Palletized Cases 75 Palletized Cases 100 Sort & Segregate 100 Sort & Segregate When I run this query I al...

yet another: which is faster table or query ?
using A2k; seen LOTS of posts about this topic, but none seem to address the heart of my question. Let's break this into 2 parts: queries (stand-alone); and form data sources (and control data sources) queries - stand alone: Let's say I have a query (#1) that relates information in 3 tables. Now I want another query (#2) that uses that same information, and relates it to more tables. Would query #2 be faster if it uses query #1, and relates that to more tables; OR would it be faster if it had all the table relations in query #1 built into it (not use a query inside a query) ? --- ...

LDAP Query for All Groups and Membership
I am looking to query Active DIrectory in an environment with Windows 2000 DC's. Its a single domain environment. Currently I have a vbs file that pulls the data but only for a single OU. I am looking for a single vbs script that pull the following information: All Groups, both security and distribution (from a single domain) Whether they are Security or Distribution Whether they are Global or Universal All members of each group I would like the output in cvs format (if possible). Like so: GroupName, MemberName, Type(Security or DL), Scope(Global or Universal). If this i...

Query/Filter asterisk
I have product descriptions that have an * in the description. I need to query or filter all products with an *. How do I that when the * is also used as a wildcard? Thanks, Scott Field: InStr([YourFieldName],Chr(42)) Ctiteria: > 0 -- KARL DEWEY Build a little - Test a little "Scott" wrote: > I have product descriptions that have an * in the description. I need to > query or filter all products with an *. How do I that when the * is also > used as a wildcard? > > Thanks, > > Scott ...

Re: Microsoft Support Queries (on Biztalk)
Hello concern, We have got some queries from Customer on Biztalk integration with IBM MQ. Customer using Biztalk Adapter for MQ Series to plug in to the MQ Queues and consumes the messages which are coming from ERPLN. The following are the question from Customer. "Could you please confirm that MQ messages are deleted once they're read by BizTalk application? If we were to pull the MQ Messages' data to 2 systems, does that mean we have to design a BizTalk application that processes the same data and send to 2 systems at the same time? Meaning, the same messages will ...

Passing query parameters thru form
I have one Pivot Chart Form (FrmChart) and form is taking data from one query. but the query generates resultsets after having the parameters value. I have another form (CallingFrm) which is calling to FrmChart form. so before i get the data in pivot chart form i should have query executed with parameters. so i am using code below: Set qryDef = CurrentDb.QueryDefs("MyQuery") qryDef.Parameters("Qtr") = ChartQtrCmb.Value qryDef.Parameters("yr") = ChartYearCmb.Value 'qryDef.OpenRecordset (dbOpenForwardOnly) 'qryDef.Execute 'I tried both but doesnt w...