TSQL-Tuesday # 18: Using a Recursive CTE to Create a Calendar Table
![TsqlLogo_thumb4[4]](http://joecasella.files.wordpress.com/2011/05/tsqllogo_thumb44.png "TsqlLogo_thumb4[4]")
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.
- DECLARE @StartDate smalldatetime = '01/01/2009' –First Calendar date to include in table
- DECLARE @EndDate smalldatetime = '12/31/2011' –Last calendar date to include in the table
- DECLARE @FiscalBeginMonth smallint = 10 –Month in which the fiscal year begins e.g. October 1
- ;
- WITH –This secton generates the number table
- E00(N) AS (SELECT 1 UNION ALL SELECT 1),
- E02(N) AS (SELECT 1 FROM E00 a, E00 b),
- E04(N) AS (SELECT 1 FROM E02 a, E02 b),
- E08(N) AS (SELECT 1 FROM E04 a, E04 b),
- E16(N) AS (SELECT 1 FROM E08 a, E08 b),
- E32(N) AS (SELECT 1 FROM E16 a, E16 b),
- cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32),
- –This CTE generates a list of calendar dates
- CalendarBase as (
- SELECT
- DateKey = n
- , CalendarDate = DATEADD(day, n - 1, @StartDate )
- , FiscalYearBegin = DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n - 1, @StartDate )) - (12 + DATEPART(MONTH, DATEADD(day, n - 1, @StartDate )) - @FiscalBeginMonth) % 12, 0)
- FROM cteTally
- WHERE N <= DATEDIFF(day, @StartDate , @EndDate +1)
- )
- –Finally, use the list of calendar dates to fill the date dimension
- SELECT DateKey
- , IsoDate = CONVERT(char(8), CalendarDate, 112)
- , CalendarDate
- , CalendarYear = YEAR(CalendarDate)
- , CalendarQuarter = (DATEPART(QUARTER,CalendarDate) )
- , CalendarMonth = MONTH(CalendarDate)
- , CalendarDay = DATEPART(DAY, CalendarDate)
- , DayofWk = DATEPART(Dw, CalendarDate)
- , CalendarWeekOfMonth = DATEDIFF(week, DATEADD(day,1, CalendarDate-DAY(CalendarDate) + 1) -1, CalendarDate) +1
- , WeekofYr = DATEPART(WEEK, CalendarDate)
- , DayofYr = DATEPART(DAYOFYEAR, CalendarDate)
- , NameMonth = DATENAME(Month, CalendarDate)
- , NameDay = DATENAME (Weekday,CalendarDate )
- , FiscalYear = CASE WHEN MONTH(CalendarDate) < @FiscalBeginMonth THEN YEAR(CalendarDate)
- ELSE YEAR(CalendarDate) + 1 END
- , FiscalMonth = DATEDIFF( MONTH, FiscalYearBegin, CalendarDate) + 1
- , FiscalWeek = DATEDIFF( WEEK, FiscalYearBegin, CalendarDate) + 1
- , FiscalDay = DATEDIFF( day, FiscalYearBegin, CalendarDate) + 1
- FROM CalendarBase
Here’s a sample of the output:
- DateKey IsoDate CalendarDate CalendarYear CalendarQuarter CalendarMonth CalendarDay DayofWk CalendarWeekOfMonth WeekofYr DayofYr NameMonth NameDay FiscalYear FiscalMonth FiscalWeek FiscalDay
- ————— ——– ———————– ———— ————— ————- ———– ———– ——————- ———– ———– ——————– ————– ———– ———– ———– ———–
- 1 20100510 2010-05-10 00:00:00 2010 2 5 10 2 3 20 130 May Monday 2010 8 33 222
- 2 20100511 2010-05-11 00:00:00 2010 2 5 11 3 3 20 131 May Tuesday 2010 8 33 223
- 3 20100512 2010-05-12 00:00:00 2010 2 5 12 4 3 20 132 May Wednesday 2010 8 33 224
- 4 20100513 2010-05-13 00:00:00 2010 2 5 13 5 3 20 133 May Thursday 2010 8 33 225
- 5 20100514 2010-05-14 00:00:00 2010 2 5 14 6 3 20 134 May Friday 2010 8 33 226
Do I get extra credit for writing two posts?
