In my first tutorial I will explain how to write a recursion SQL statement to grab all the days in the past year into a view. I was recently working on a small project at work were I needed to find all the dates in the past year. The database I was working with did not have this information so I wanted to create a view to populate this information and just call that view when needed.
I did some goggling and searching the web and found some sites that helped me accomplish the task at hand. I always want to give credit where credit is needed. I would like to thank Jes Borland and Colin Stasiuk on their tutorials to help me accomplish my goal. There web pages are linked below respectively.
1. Jes Borland webpage
2. Colin Stasiuk webpage
Jes's blog helped me understand how to write a recursion CTE statement where as Colin's blog helped me understand Maxrecursion with a CTE in a view. After reading Jes's blog I was able to create the following statement.
1: WITH DateCTE (CalendarDate, DateYear, DateQuarter, DateMonth, DateWeek, DateDayOfWeek, DateDay, DateDayOfYear) AS
2: (
3: SELECT getdate(),
4: DATEPART(YY, getdate()),
5: DATEPART(QQ, getdate()),
6: DATEPART(MM, getdate()),
7: DATEPART(WW, getdate()),
8: DATEPART(DW, getdate()),
9: DATEPART(DD, getdate()),
10: DATEPART(DY, getdate())
11: UNION ALL
12: SELECT DATEADD(DD, 1, CalendarDate),
13: DATEPART(YY, DATEADD(DD, 1, CalendarDate)),
14: DATEPART(QQ, DATEADD(DD, 1, CalendarDate)),
15: DATEPART(MM, DATEADD(DD, 1, CalendarDate)),
16: DATEPART(WW, DATEADD(DD, 1, CalendarDate)),
17: DATEPART(DW, DATEADD(DD, 1, CalendarDate)),
18: DATEPART(DD, DATEADD(DD, 1, CalendarDate)),
19: DATEPART(DY, DATEADD(DD, 1, CalendarDate))
20: FROM DateCTE
21: WHERE DATEADD(DD, 1, CalendarDate) <= DATEADD(YY, 1, getdate())
22: )
23: SELECT CalendarDate, DateYear, DateQuarter, DateMonth, DateWeek, DateDayOfWeek, DateDay, DateDayOfYear
24: FROM DateCTE AS DC
25: OPTION(MAXRECURSION 750)
When I saved my CTE view with MAXRECURSION I would get the following error message.
Incorrect syntax near the keyword ‘OPTION’.
After reading Colin's web page I found out you can't call the MAXRURSION within in the view, but rather when you call the view itself. So My final version of the view is as follow.
1: WITH DateCTE (CalendarDate, DateYear, DateQuarter, DateMonth, DateWeek, DateDayOfWeek, DateDay, DateDayOfYear) AS
2: (
3: SELECT getdate(),
4: DATEPART(YY, getdate()),
5: DATEPART(QQ, getdate()),
6: DATEPART(MM, getdate()),
7: DATEPART(WW, getdate()),
8: DATEPART(DW, getdate()),
9: DATEPART(DD, getdate()),
10: DATEPART(DY, getdate())
11: UNION ALL
12: SELECT DATEADD(DD, 1, CalendarDate),
13: DATEPART(YY, DATEADD(DD, 1, CalendarDate)),
14: DATEPART(QQ, DATEADD(DD, 1, CalendarDate)),
15: DATEPART(MM, DATEADD(DD, 1, CalendarDate)),
16: DATEPART(WW, DATEADD(DD, 1, CalendarDate)),
17: DATEPART(DW, DATEADD(DD, 1, CalendarDate)),
18: DATEPART(DD, DATEADD(DD, 1, CalendarDate)),
19: DATEPART(DY, DATEADD(DD, 1, CalendarDate))
20: FROM DateCTE
21: WHERE DATEADD(DD, 1, CalendarDate) <= DATEADD(YY, 1, getdate())
22: )
23: SELECT CalendarDate, DateYear, DateQuarter, DateMonth, DateWeek, DateDayOfWeek, DateDay, DateDayOfYear
24: FROM DateCTE AS DC
To call be able to call the view and get all the previous dates all you have to do is write the following code.
1: Select * from vPastYearDAtes
2: OPTION(MAXRECURSION 750)
Thank you for reading my first post and be sure to check back to see more in the upcoming weeks!
Robert
