recursive cte question

Question

Post reply

recursive cte question

sqlpadawan_1

SSCommitted

Points: 1571
More actions
August 4, 2011 at 2:32 pm

#241190

*disclaimer, this is from beyondrelational.com puzzle #61. i'm not looking for the answer, but looking for direction on a recursive cte. not even sure if i'm headed down the right rabbit hole, but that's beside the point. it's become a recursive cte question.*

So i have a table that looks like:

id date

-- ----

1 2011-03-01

1 2011-03-02

1 2011-03-05

2 2012-03-01

2 2012-03-03

I want to build a table that includes all dates between the min and max date for the given id, so i should end up with something like this:

id date

-- ----

1 2011-03-01

1 2011-03-02

1 2011-03-03 <-- record added

1 2011-03-04 <-- record added

1 2011-03-05

2 2012-03-01

2 2012-03-02 <-- record added

2 2012-03-03

The following code works, but only for one id. If i comment the line 'WHERE ID = 1', the recursion goes nuts. I got to be missing something in the recursive cte, but i'm stuck.

USE tempdb GO CREATE TABLE #TableOfDates ( ID int, TheDate DATETIME ) INSERT INTO #TableOfDates (ID, TheDate) SELECT 1, '2011-03-01 00:00:00' UNION SELECT 1, '2011-03-02 00:00:00' UNION SELECT 1, '2011-03-05 00:00:00' UNION SELECT 2, '2012-03-01 00:00:00' UNION SELECT 2, '2012-03-03 00:00:00' ;WITH cteMinMax(ID, MinDate, MaxDate) AS ( SELECT ID, MIN([TheDate]) AS MinDate, MAX([TheDate]) AS MaxDate FROM #TableOfDates WHERE ID = 1 GROUP BY ID ), cteCalendar ( ID, TheDATE, DayOfWk) AS ( SELECT cteMinMax.ID, MinDate, DATENAME(dw,MinDate) FROM cteMinMax UNION ALL SELECT cteMinMax.ID, DATEADD(dd,1,TheDATE), DATENAME(dw,DATEADD(dd,1,TheDATE)) FROM cteCalendar, cteMinMax WHERE DATEADD(dd,1,TheDATE) < = cteMinMax.MaxDate ) SELECT ID, TheDate, DayOfWk FROM cteCalendar DROP TABLE #TableOfDates

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12305 More actions · Answer 1

You've got a cross join in your cte.

Change the recursive part like this and you're done:

SELECT m.ID,



        DATEADD(dd,1,c.TheDATE),

        DATENAME(dw,DATEADD(dd,1,c.TheDATE))

 FROM cteCalendar c

	inner join cteMinMax m on (m.ID = c.ID)

WHERE DATEADD(dd,1,c.TheDATE) < = m.MaxDate

Recursion is not the way to go about on this one, though. You'll run into the first signs of problems when one of your date range goes beyond 100 days: You'll get an error saying you've exhuasted the maximum recursion level. You'd be better of generating a calendar using a numbers list. You will have maximum recursion depth of 2: level 1 returns all existing dates, level 2 adds all missing dates. Or better yet, use the dates table only to get the min and max values and then generate the entire list from the numbers table, no recursion at all! More information on tally tables and their usage: http://qa.sqlservercentral.com/articles/62867/.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

R.P.Rozema (8/4/2011)

Recursion is not the way to go about on this one, though. You'll run into the first signs of problems when one of your date range goes beyond 100 days:

That limit can easily be fixed with OPTION(MAXRECURSION 0) or some number less that a bit more than 32,000 if you want to keep a limit on it. 😉

The real key is that doing this kind of "counting" using an rCTE will absolutely blow "reads" through the roof. Because it's a contest, I'm not going to tell you how to do it right... I just want you to know not to do it wrong. 😉

--Jeff Moden

RBAR 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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sqlpadawan_1 SSCommitted Points: 1571 More actions · Answer 3

sqlpadawan_1

SSCommitted

Points: 1571

August 5, 2011 at 4:08 am

#1364206

Thanks all for the input and direction. I think I'm going to try and do it both ways to see what the difference is in the execution plans.

R.P.Rozema SSChampion Points: 12305 More actions · Answer 4

I had overlooked the contest comment (or better: I incorrectly interpreted the implications). Luckily I wasn't too complete in my answer :-D. Then again, all information you may need is already available on this forum, I probably only made your search a little easier.

Good luck in the contest.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

sqlpadawan_1 (8/5/2011)

Thanks all for the input and direction. I think I'm going to try and do it both ways to see what the difference is in the execution plans.

I' advise very, very strongly against that. The execution plan LIES especially when it come to recursion where it only costs the FIRST iteration. Use SQL Profiler, instead.

--Jeff Moden

RBAR 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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions