## Solve Problems Using Recursive CTE

 Author Message bj_shenglong Forum Newbie Group: General Forum Members Points: 8 Visits: 63 [quote]mickyT (12/5/2012)So comparing the islands and recursive queries returning similar rows`;WITH cte AS (   SELECT name,       DATEADD(mm, - ROW_NUMBER() OVER (ORDER BY name, saledate), saledate) dategroup,      saledate   FROM #sales    WHERE quantity > 2 and saledate >= '2012-01-01'   SELECT name, max(saledate), COUNT(*)FROM cteGROUP BY name, dategroupHAVING COUNT(*) > 1 ORDER BY name, dategroup;with m2_cte_f (name,saledate,quantity,ind) as (select s.*, 0 as indfrom #sales swhere s.saledate='2012-01-01'union allselect s.*, case when s.quantity > 2 and sc.quantity > 2 then 1 else 0 end as indfrom #sales sinner join m2_cte_f sc on (s.saledate = dateadd(month,1,sc.saledate) and s.name=sc.name)where sc.ind = 0 )select * from m2_cte_f where ind=1 `I get the following IO stats (timing not worth mentioning 1ms each) for the small test set`(3 row(s) affected)Table '#sales____00000000009F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(3 row(s) affected)Table 'Worktable'. Scan count 2, logical reads 91, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#sales____00000000009F'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.`Upping the stakes a tiny bit by putting a moderate amount of data (3000 odd rows) into the table`INSERT INTO #sales (name, saledate)SELECT *FROM   (SELECT * FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T')) as sales(name)) names,   (SELECT TOP 156 dateadd(mm, N, '1999-12-01') saledate FROM Tally) as months UPDATE #salesSET quantity = RAND(Checksum(Newid())) * 5CREATE CLUSTERED INDEX SALES_IDX1 ON #sales (saledate, name)`Thanks for all updates on this topic.I have one question for this test.It seems the test data starts at 1999-12-01, but queries use '2012-01-01' to start. I am not sure what the end date is.I was wondering if it makes any difference after we make some change on start date in these 2 queries, so that start date = the earlies test date, then sorting may go through all period for query 1. Of course, recursion will have more joins for query 2. mickyT Ten Centuries Group: General Forum Members Points: 1253 Visits: 3309 bj_shenglongI have one question for this test.It seems the test data starts at 1999-12-01, but queries use '2012-01-01' to start. I am not sure what the end date is.I was wondering if it makes any difference after we make some change on start date in these 2 queries, so that start date = the earlies test date, then sorting may go through all period for query 1. Of course, recursion will have more joins for query 2.I put the date into the query to make the Island query similar to the last query you posted. That query will only report the first consecutive month for each name, however the Island query will report all consecutive months in the period for a name. For exampleA 2000-01-01 2 A 2000-02-01 3A 2000-03-01 4 <-- Returned by RecursiveA 2000-04-01 3 <-- Returned By IslandA 2000-05-01 1A 2000-06-01 2A 2000-07-01 1A 2000-08-01 3A 2000-09-01 3A 2000-10-01 3A 2000-11-01 4 <-- Returned By Island A 2000-12-01 2What I found interesting was even though I was only adding data prior to those dates the recursive query kept getting more expensive. bj_shenglong Forum Newbie Group: General Forum Members Points: 8 Visits: 63 I put the date into the query to make the Island query similar to the last query you posted. That query will only report the first consecutive month for each name, however the Island query will report all consecutive months in the period for a name. For exampleThat is what I said in previous post. This perticular task is to find when someone are qualified at his first time. So, recursion will search at beginning and stop searching after reaching the goal. Jeff Moden SSC-Forever Group: General Forum Members Points: 44994 Visits: 39880 bj_shenglong (12/6/2012)I put the date into the query to make the Island query similar to the last query you posted. That query will only report the first consecutive month for each name, however the Island query will report all consecutive months in the period for a name. For exampleThat is what I said in previous post. This perticular task is to find when someone are qualified at his first time. So, recursion will search at beginning and stop searching after reaching the goal.I've haven't looked at the queries in any great detail but I'm thinking that add TOP 1 would easily solve such a problem. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code:      Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs