This month’s TSQL-Tuesday topic is so much fun to write about that I’ve decided to create a second post.
When Recursive CTEs were introduced in SQL Server 2005, many people gave examples of how they can be used to generate a “numbers” table. (I’ll ignore the debate about whether it’s the best way to generate the table, or if we should even care). Well, once you have a numbers table it’s easy to create a Calendar table or Date Dimension.
The script consists of three parts. The first section is a recursive CTE, using a method attributed to Itzik Ben-Gan, that generates a numbers table. In the second section, each number in the table is added to your starting date to generate a list of days between the start and end dates. Each date is also associated with the first date of its fiscal year using a creative approach I learned from this forum post. This “first date” of a fiscal year will be used to perform the fiscal date calculations in the final step. Finally, the days CTE is used with a series of date functions to populate the calendar table or date dimension.
Here’s a sample of the output:
Do I get extra credit for writing two posts?