Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

TSQL recursive CTE challenge...

Building a recursive CTE that:
- Displays 365 or 366 days dates (2917-1-1, 2017-1-2, etc. for whole year) in [DateFiled] column. This maybe no challenge for you. Among some other solution you may have for this, I am curious if it could be done using a recursive CTE?

A table valued function or a PROC will be straightforward but building a CTE requires someone who is most comfortable with recursive  CTE in general.

thx
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Unfortunally you can't do that with a CTE. At least I couldn't. My test returned me an error of maximum recursion limit reached:
;WITH CTE_GenDate
AS (
	SELECT DATEFROMPARTS(YEAR(GETDATE()),1,1) DayYear
	UNION ALL
	SELECT DATEADD(day,1,DayYear) 
	FROM CTE_GenDate
	WHERE DayYear < DATEFROMPARTS(YEAR(GETDATE()),12,31) 
	)
SELECT *
FROM CTE_GenDate

-------------------------------------------
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Open in new window

SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike Eghtebas

ASKER

@Robert, Thank you for help.

@Vitor,
This is grate.  Could we introduce the year into it, hard-coded or via a variable?

@_agx_, Thank you for the endorsements of the experts.

Mike
Could we introduce the year into it, hard-coded or via a variable?
Sure. I just assumed that you wanted for the current year so I placed the YEAR(GETDATE()) on it. You can replace that with an hard-coded year or an INT variable that has the year stored.
Just to make you aware, a recursive CTE is far less efficient for this process than a tally table, either an inline or even a hard-coded one.
@all,

FYI, I tried for 2001 and it worked for OPTION (MAXRECURSION 364) to OPTION (MAXRECURSION 32767) range.

Thank you very much.

Mike
Hi Scott,

The goal here was to learn and practice CTE. There was no need to build such a table other than practice.

Thanks,

Mike