Recursive CTE for Dates In A Year

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.

Recursive Review

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

See picture below: he CTE is named Dates and 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.

&amp;lt;/span&amp;gt;
 ;WITH Dates as
 (
 SELECT cast('2013-01-01' as date) as CalendarDate&lt;/span&gt;

UNION ALL

SELECT dateadd(day , 1, CalendarDate) AS CalendarDate
 FROM Dates
 WHERE dateadd (day, 1, CalendarDate) &amp;lt; '2014-01-01'
 )

SELECT *
 FROM Dates
 OPTION (MAXRECURSION 366);
&amp;lt;pre&amp;gt;

How would this be useful?:

Chapter 10 of the CTE book has a section on finding holes in patterns. Basically if you want to query for things you have, it is generally straightforward, but if you want to query for things that you don’t have it is not as easy. Let’s say you are working on a scheduling application. This app needs to look at a list of dates and find the dates that a venue may be available. You can easily query the dates the venue is in use. With the Dates CTE you could 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.

Download my book for FREE when you sign up for my mailing list.

Receive your free copy today!