DataWriter

May 10, 2011

TSQL-Tuesday # 18: Using a Recursive CTE to Create a Calendar Table

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 6:20 am
Tags: , ,

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.

  1.  
  2.  
  3. DECLARE @StartDate smalldatetime = '01/01/2009' –First Calendar date to include in table
  4. DECLARE @EndDate smalldatetime = '12/31/2011' –Last calendar date to include in the table
  5. DECLARE @FiscalBeginMonth smallint = 10  –Month in which the fiscal year begins e.g. October 1
  6. ;
  7.  
  8.    WITH –This secton generates the number table
  9.         E00(N) AS (SELECT 1 UNION ALL SELECT 1),
  10.         E02(N) AS (SELECT 1 FROM E00 a, E00 b),
  11.         E04(N) AS (SELECT 1 FROM E02 a, E02 b),
  12.         E08(N) AS (SELECT 1 FROM E04 a, E04 b),
  13.         E16(N) AS (SELECT 1 FROM E08 a, E08 b),
  14.         E32(N) AS (SELECT 1 FROM E16 a, E16 b),
  15.    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32),
  16.     –This CTE generates a list of calendar dates
  17. CalendarBase as (
  18. SELECT
  19.         DateKey = n
  20.       , CalendarDate = DATEADD(day, n 1, @StartDate )
  21.       , FiscalYearBegin = DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n 1, @StartDate )) (12 + DATEPART(MONTH, DATEADD(day, n 1, @StartDate )) @FiscalBeginMonth) % 12, 0)
  22.    FROM cteTally
  23.   WHERE N <= DATEDIFF(day, @StartDate , @EndDate +1)
  24. )
  25.  
  26. –Finally, use the list of calendar dates to fill the date dimension
  27. SELECT DateKey
  28.     , IsoDate       = CONVERT(char(8), CalendarDate, 112)
  29.     , CalendarDate
  30.     , CalendarYear  = YEAR(CalendarDate)
  31.     , CalendarQuarter =  (DATEPART(QUARTER,CalendarDate) )
  32.     , CalendarMonth = MONTH(CalendarDate)
  33.     , CalendarDay   = DATEPART(DAY, CalendarDate)
  34.     , DayofWk       = DATEPART(Dw, CalendarDate)
  35.     , CalendarWeekOfMonth = DATEDIFF(week, DATEADD(day,1, CalendarDateDAY(CalendarDate) + 1) 1, CalendarDate) +1
  36.     , WeekofYr      = DATEPART(WEEK, CalendarDate)
  37.     , DayofYr       = DATEPART(DAYOFYEAR, CalendarDate)
  38.     , NameMonth     = DATENAME(Month, CalendarDate)
  39.     , NameDay       = DATENAME (Weekday,CalendarDate )
  40.     , FiscalYear    = CASE WHEN MONTH(CalendarDate)  < @FiscalBeginMonth THEN  YEAR(CalendarDate)
  41.                            ELSE YEAR(CalendarDate) + 1 END
  42.     , FiscalMonth    = DATEDIFF( MONTH, FiscalYearBegin, CalendarDate) + 1
  43.     , FiscalWeek     = DATEDIFF( WEEK, FiscalYearBegin, CalendarDate) + 1
  44.     , FiscalDay      = DATEDIFF( day, FiscalYearBegin, CalendarDate) + 1
  45.   FROM CalendarBase

Here’s a sample of the output:

  1. DateKey         IsoDate  CalendarDate            CalendarYear CalendarQuarter CalendarMonth CalendarDay DayofWk     CalendarWeekOfMonth WeekofYr    DayofYr     NameMonth            NameDay        FiscalYear  FiscalMonth FiscalWeek  FiscalDay
  2. ————— ——– ———————– ———— ————— ————- ———– ———– ——————- ———– ———– ——————– ————– ———– ———– ———– ———–
  3. 1               20100510 2010-05-10 00:00:00     2010         2               5             10          2           3                   20          130         May                  Monday         2010        8           33          222
  4. 2               20100511 2010-05-11 00:00:00     2010         2               5             11          3           3                   20          131         May                  Tuesday        2010        8           33          223
  5. 3               20100512 2010-05-12 00:00:00     2010         2               5             12          4           3                   20          132         May                  Wednesday      2010        8           33          224
  6. 4               20100513 2010-05-13 00:00:00     2010         2               5             13          5           3                   20          133         May                  Thursday       2010        8           33          225
  7. 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?

Advertisements

3 Comments »

  1. Hi Joe –

    Thanks for contributing both great posts to T-SQL Tuesday!

    Comment by Bob Pusateri — May 10, 2011 @ 10:55 pm | Reply

  2. WITH Sales_CTE (date)
    AS
    — Define the CTE query.
    (
    Select cast(‘1 aug 2011’ as datetime) as date
    UNION ALL
    Select date + 1 as date from Sales_CTE where date +1 < '1 sep 2011'
    )
    select [1] as sun, [2] as mon, [3] as tue, [4] as wed, [5] as thu, [6] as fri , [7] as sat
    FROM
    (select day(date) as MONTHDATE,datepart(weekday,date) AS DAYOFWEEK,row_number() over(order by date)/7 as ROW from Sales_CTE ) as source_table
    pivot ( sum(monthdate)
    for dayofweek in ([1],[2],[3],[4],[5],[6],[7])) as pivottable

    Comment by abk — August 22, 2011 @ 10:07 am | Reply

  3. Thanks for the nice Calendar table!
    Based on the 2nd comment, the following corrects the problem with the row assignments of the days in the rendered calendar grid:

    CREATE PROCEDURE dbo.spGetMonthCalendar
    @Month INT = 1,
    @Year INT = 2011
    AS BEGIN

    DECLARE @StartDate DATE
    DECLARE @EndDate DATE

    — Create the start date value
    SET @StartDate = CONVERT(DATE, RIGHT(‘0000’ + CONVERT(VARCHAR, @Year),4) + ‘-‘ + RIGHT(’00’ + CONVERT(VARCHAR, @Month), 2) + ‘-01’)
    — Create the end date
    SET @EndDate = DATEADD(M,1,@StartDate)

    ;WITH Sales_CTE (MonthDate)
    AS
    (
    Select
    @StartDate
    UNION ALL
    Select DATEADD(dd, 1, MonthDate) as MonthDate
    from Sales_CTE
    WHERE
    DATEADD(dd, 1, MonthDate) < @EndDate
    )
    select
    [1] as sun,
    [2] as mon,
    [3] as tue,
    [4] as wed,
    [5] as thu,
    [6] as fri,
    [7] as sat
    FROM
    (
    select
    day(MonthDate) as MONTHDATE,
    datepart(weekday, MonthDate) AS DAYOFWEEK,
    DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, MonthDate), 0), MonthDate) +1 as ROW — this correctly determine which week of the month the day falls on (from http://www.berezniker.com/content/pages/sql/microsoft-sql-server/week-number-month)
    –row_number() over(order by MonthDate)/7 as ROW –this renders days to the wrong row sometimes
    from Sales_CTE
    ) as source_table
    pivot (
    sum(monthdate)
    for dayofweek in ([1],[2],[3],[4],[5],[6],[7])
    ) as pivottable
    END

    Comment by Jason Cox — November 15, 2011 @ 9:35 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: