Expanding on Recursive Dates CTE

Day 9 of Common Table Expression Month (June) at SteveStedman.com, today I will be building on the intro to recursive CTEs from two days ago and on showing how a recursive CTE can be used to calculate information about dates of the year from yesterday. 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.

Dates CTE Review

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.

Adding In More Details

For today’s example the CTE will be exactly the same as yesterday, but the query calling it is different. The part highlighted below shows the difference from yesterday.

When run we get the following output:

Next I borrow part of the date formatting from @DevonDBA from his post at http://devondba.blogspot.co.uk/2013/06/create-table-of-dates-showing-holidays.html, and slightly modify it as shown below. Thanks Martyn.

Now we can see if the day that we are looking at is a weekday or a weekend as shown here:

I hope you are enjoying CTE Month at SteveStedman.com.

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.