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: 12317 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://www.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: 1004406 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)

sqlpadawan_1 SSCommitted Points: 1571 More actions · Answer 3

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: 12317 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.