Mike Eghtebas
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
- 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
@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.
ASKER
@all,
FYI, I tried for 2001 and it worked for OPTION (MAXRECURSION 364) to OPTION (MAXRECURSION 32767) range.
Thank you very much.
Mike
FYI, I tried for 2001 and it worked for OPTION (MAXRECURSION 364) to OPTION (MAXRECURSION 32767) range.
Thank you very much.
Mike
ASKER
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
The goal here was to learn and practice CTE. There was no need to build such a table other than practice.
Thanks,
Mike
Open in new window