#### 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
1023 Views

Similar Articles

[PageSpeed] 58

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

VS barks at this recursive schema
Can some one help me out here, VS doesn't like this schema ("SubSystem is not defined"). Anyone know why? <xs:complexType name="SystemType"> <xs:sequence maxOccurs="unbounded"> <xs:choice maxOccurs="unbounded"> <xs:element minOccurs="0" maxOccurs="unbounded" name="SubSystem"> <xs:complexType> <xs:sequence> <xs:element ref="Operation" minOccurs="0" maxOccurs="unbounded"/> <xs:element ref="SubSy...

Query Criteria!
Only records that are 3 years old, <1095 days Is this possible Thanks for any help....Bob This does not work because Date field is Text <Date()-"1095" Thanks for any help..Bob "Bob V" <rjvance@ihug.co.nz> wrote in message news:eB44rOm0HHA.5644@TK2MSFTNGP05.phx.gbl... > > > Only records that are 3 years old, <1095 days Is this possible Thanks for > any help....Bob > On Mon, 30 Jul 2007 17:03:57 +1200, "Bob V" <rjvance@ihug.co.nz> wrote: >This does not work because Date field is Text <Date()-"1095" ...

Recursion Question
I have the following simple recursion and as I unwind the call stack (stepping through it), I am trying figure out where the eventual return value is being stored. Any help is appreciated. Thanks Mike namespace Factorial { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { label1.Text = Factorial(5).ToString(); } public int Factorial(int n) { if(n==0) return 1; ...

Total and query
I have a form frmreferb which has a sunbform called frmreferbdetails (Continues Form) on the subform I have a text box called quantity and a Priceperunit text box. I have a Total Text box with the control source set to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I would also like to do is calculate the total text box to give me a grand total on the frmreferb form. Does anyone have any ideas? The other thing I am trying to do is I have created a query called ReferbTotalsQuery this contains the quantity and total of each item. I need to calculate the total of each li...

recursively search public folders
Using Outlook 2003, I can search my private email folders recursively with no problem. Also I can specify multiple folders, selecting them individually. But if I try that with our Public Folders, it says that I can't search recursively nor can I select multiple folders at a time. Is there a way to do this, and I'm just missing it? Doesn't anybody here know, one way or another, whether this is possible? I can accept if the answer is no, but I'm surprised not one person has answered either way. Any help is appreciated. "Mickey Ferguson" <MFerguson@pei...

Conversion of MS Access queries to SQL queries
Hi All, Anyone knows of a utility or software that will convert MS Access queries to SQL Queries. I need to convert my existing ms access queries to SQL queries as i need to use them in a software which only takes SQL queries. I tried to copy and paste "SQLVIEW" of the queries from MSaccess but they dont' work as they require modifications. I don't have much knowledge of SQL queries, wondering if someone can point me in right direction. Thx In article <1181230883.726006.250120@q66g2000hsg.googlegroups.com>, raonhassan@yahoo.com says... > Hi All, > Anyone knows...

Query on multiple servers, sort by server name
Hello I'm running a query on the sysjobhistory table on multiple servers. This works fine, except that the results are not sorted by server name, which is quite annoying. As the column "server name" is not part of the query, is there a way to sort by this column ? Pierrot Hi Pierrot How about: select @@ServerName Sname, * from msdb..sysjobhistory order by @@ServerName desc Jon "wpher56" wrote: > Hello > I'm running a query on the sysjobhistory table on multiple servers. This > works fine, except that the results are no...

Web Queries #3
I have a worksheet that contains several web queries. When I try to refresh them sometimes it works, but most of the time I get error messages. Can anybody let me know why there is this inconsistency. ...

Beginner's Append Query Problem
I was trying to follow the instructions on http://www.databasedev.co.uk/reset_autonumber.html but I couldn't choose the table to which I would have appended because that table wasn't on the list in the Append dialog box. Why is this and how to fix? p.numminen@suomi24.fi wrote: > I was trying to follow the instructions on http://www.databasedev.co.uk/reset_autonumber.html > but I couldn't choose the table to which I would have appended because > that table wasn't on the list in the Append dialog box. Why is this > and how to fix? -----BEGIN PGP SIGNED MESSAGE----- ...

help with query 03-19-10
sql server 2008 sp1 I can't seem to get this query right. I need a way to read a table and for each person, show the person's highest value and the person's last entered value to get a result set like this person_id high last ------------- ----- ----- 1 40 20 2 30 15 here is a sample schema create table test (person_id int, enter_value int, enter_date datetime) insert into test values (1, 30, '2001-01-01') insert into test values (1, 40, '2002-01-01') insert into test values (1, 10, '...

query two tables
Hi, I am very new to Access and am having some trouble. I have two tables in my DB, one called Data that contains approx 14,000 names and another table called Returned that has approx 1000 names. I want to create a query to remove the Returned names from the list of names in the Data table so that the resulting query only gives me the remaining 13,000 names that were NOT in the returned table. I tried creating a query with a Criteria in the name filed of "[data]![Name] Not Like [Returned]![Name]". This results in a total of 1000 names in the query instead of the 13,000 I wou...

Hi, Can anyone help with how can I query AD for Account containing specific word in the DisplayName. I am using Custom SSearch in AD and I have to write the LDAP query which needs to display all accounts where DisplayName contains test. Display all Accounts where DisplayName contains (Test) Sorry I'm new to LDAP and I need this to be in AD. Thank you, Lion Howdie! Am 02.06.2010 17:32, schrieb Lion@hotmail.com: > Can anyone help with how can I query AD for Account containing specific > word in the DisplayName. I am using Custom SSearch in AD and I hav...

c++ recursive class
Hello everyone!!! I'd need a little hint on this little question: I have a xml file I want to navigate and I have a library to process it. But I want also to manage that file as an object to make clear and efficient code. So I coded two class which one is like recursive as like as in this example: class B : public xmlnode { public: B(xmlnode* member); LPCSTR getattribute(....); void setattribute(....); xmlnode* Node(LPCSTR nodename, LPCSTR attributenodename, LPCSTR attributetosearch); }; class A : public xmldocument { public: ...

Thoughts on #ifdef, #define statements
I am not a big fan of the C/C++ preprocessor directives #ifdef or #ifndef. I am not denying that they certainly have their place and usage in the language. I'll first write about where I think they are useful and then about the situations where I feel they are not. #ifndef is very useful for preventing multiple inclusions of a header which will cause redefinition errors. #ifndef __FOO_H__ // only proceed if the token is not yet defined #define __FOO_H__ // define the token so that subsequent includes have it defined [...contents of the header...] #endif // __FOO_H__, end ...

IIF Query?
Hi - I need to run a query for a mail merge and need to have a "addressee" field. My table has two name fields, "first" name and "second" name. If there is a "second" name listed, I want to use that name, but if there is not a "second" name listed, I want to use the "first" name listed. This is what I wrote , but it doesn't return anything. I think I am doing something wrong. addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not Null,[second_name]," ")) Thanks, BS On Oct 9, 8:47 pm, bluesk...

need help with query or report
I am needing to print out an inventory list that is in a box. I keep track of paperwork by using numbers from various sources (not able to change the sources) but each source has a number given to it. I keep each source serapate from each other in didferent boxes. I need to print out an inventory list that is in each box. I am just beginning using Access 2007. I have all 4 tables with data from each source ie. scanned forms, audit files and need to print out what is in each box. >> I am needing to print out an inventory list that is in a box. You need to use a term that corre...

CInternetSession.OpenURL() recursive call fails....
Hi, ENV: Windows XP, VS Studio 2003 with MFC [SUMMARY] My recursive call to CInternetSession.OpenURL() fails, as Close() is probably not resetting the handle to NULL. [IN DETAIL] My recursive call to CInternetSession.OpenURL() fails with system error saying about invalid handle. I am calling Close() on the session object after my first invocation of OpenURL(). However, as the Close() returns nothing, I have no way to know if it could close the session with success. I guess its closing the session, but the handle to session is not reset to null, which probably breaks my recursive call. Here ...

Want to create SQL query for updating records
I am using the Query below to extract data from a table: select refnbr,replace(reverse(left(reverse(url),charindex('%',reverse(url))-3)),'%',''),url from xep_aptran where url <> '' order by refnbr The data that is extracted when I run the query is as below Refnbr (No Coloum Name) URL 000199 CAON00000013 https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000013 000200 CAON00000011 https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000011 What I really want to ...

Help with Loop Apend Query in VBA
I have a table of 14 colums, first 2 are text and the last 12 are numeric with the field name eg. Jan-08, Feb-08, etc I need to take this data month by month and append it to a table of 4 colums with the first 2 colums as text then a date field and then a value field. What is the VBA code for DoCmd.RunSQL and the loop code I need to use? Regards Chase You don't have a database, you have a spreadsheet. You have back yourself into a really ugly corner. Having field names like Jan-08, etc means every year you have to change your table field names and all code that reference these...

Difficult query?
Dear friends, I have a database in Access 2003 about Forest Inventory. I have data collected from sample plots in a table (T_RawData1991-2001 – linked from T_SamplePlotsDetails one to many). For each tree I have its SerialNo recorded manually, so in a sample plot I may have 5 trees (up to 100), serially 1-5. I have also recorded the distance and the azimuth of each tree from the center of the sample plot. Now, I want to have (using a query perhaps) the following results: The distance combination between all Trees e.g. 1-2, 1-3, 1-4, 1-5, 2-1, 2-3, 2-4, 2-5, 3-1, 3-2, 3-4, 3-5, 4-1...

Unable to join 3rd subquery in nested query
I am able to correctly nest 2 queries into one, but am unable to successfully get the third query to work. I do this all the time in Pass-Through, but this is a straight Access query. So this works: SELECT R.AUID, L.Attainment_Desc_Abbrv, R.Attainment_Desc_Abbrv FROM (SELECT [Assessment Table].AUID, ADB_Attainment_Abbrv.Attainment_Desc_Abbrv FROM ADB_Attainment_Abbrv RIGHT JOIN [Assessment Table] ON ADB_Attainment_Abbrv.Attainment_Desc = [Assessment Table].ARUS) AS R INNER JOIN (SELECT [Assessment Table].AUID, ADB_Attainment_Abbrv.Attainment_Desc_Abbrv FROM ADB_Attainment...

Noob Query
Hello Gurus, I have inherited a db that has two tables. Table 1 has a Names field and info in other fields. It has 847 records. Table 2 has a Names field and different info in other fields. It has 693 records. The 693 Names in Table 2 are in Table 1 but I need the list of Names from Table 1 that are NOT in Table 2. I can't get my head round the query that I need (Access 2003) Any pointers would be appreciated. Joskin Check out the Unmatched Query wizard when you select New Query. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want an...

UDF XLA and SQL queries
Newbie question. Apologies in advance if it's too simplistic or in the wrong forum. I want to add an UDF to Excel. This UDF queries a SQL database. Say the function name is 'MyFunc'; I want to be able to have (in a cell) =MyFunc("ABC") return some data from the SQL database (for example here, say the Product Description for product "ABC"). The user needs to select the database the first time he uses the workbook. The UDF is called often in the worksheets of a given workbook so I would prefer to open the connection once at the beginning and...

Get full path directory list on both recursive and non-recursive dir command
This post could easily be titled "Why Unix is so much better than Windows", but that would likely ruffle a few feathers... I have the following seemingly simple requirements: 1) Directory list of *.foo files, recursive to a particular root directory 2) Directory list of *.foo files, in the root directory only The list has to be returned as a full path to the files. What works (returns full path) 1) dir "C:\Path\To\My\Files\*.foo" /b /s What fails (returns filename only) 2) dir "C:\Path\To\My\Files\*.foo" /b I've also investigated t...

Query Enhancement Request
Hello again, Yesterday I got some great help on my problem with my query and I would like to enhance on that a little with your help. here is the current SQL I'm using... SELECT tbl_main.cbID, Mid([CBnumRemarks],7,4) AS sort1 FROM tbl_main GROUP BY tbl_main.cbID, Mid([CBnumRemarks],7,4) HAVING (((Mid([CBnumRemarks],7,4))>=1)) ORDER BY Mid([CBnumRemarks],7,4); This takes "CB-01-1101-09-07", breaks it down to just "1101" and sorts it for me. Which is great, but is there a way to have this "Show" me all the Missing or out of place data? The data should...