Day 8 of Common Table Expression Month (June) at SteveStedman.com, today I will be building on the intro to recursive CTEs from yesterday and showing how a recursive CTE can be used to calculate information about dates of the year. This would be useful if you were trying to build a calendar.
These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.
Yesterday’s topic was the introduction to recursive CTE’s.
In the introduction to recursive CTE’s we covered the declaration of the CTE, the Anchor Query which starts the recursive process, the Recursive Query which continues the recursion, and the Query that calls the CTE.
Recursive CTE for dates in a Year
In the following picture, the CTE is named Dates, the anchor query start out by just selecting January 1st of 2013. Next the recursive part selects CalendarDate from the Dates CTE and it adds a single day to it. This all continues recursively as long as the date is less than January 1st 2014.
There is an additional setting. The OPTION (MAXRECURSION 366) has been added to go past the default 100 levels of recursion.
When we run the query we get the following results:
Which continues all the way to December 31st 2013.
</span> ;WITH Dates as ( SELECT cast('2013-01-01' as date) as CalendarDate UNION ALL SELECT dateadd(day , 1, CalendarDate) AS CalendarDate FROM Dates WHERE dateadd (day, 1, CalendarDate) < '2014-01-01' ) SELECT * FROM Dates OPTION (MAXRECURSION 366); <pre>
How Would This Be Useful:
Chapter 10 of the CTE book has a section on finding holes in patterns. Basically you want to query for things that you have it is generally straightforward, but if you want to query for things that you don’t have it is not as easy. Lets say you are working on a scheduling application that needs to look at a list of dates and find the dates that a venue may be available. You can probably easily query the dates the venue is in use, and with the Dates CTE you could then do a left join with exclusions where you left join the Dates CTE to the dates that a venue is in use, then only select the results where the venue date is null. This would return the dates that the venue is available.
Common Table Expressions Book
If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.