Create a calendar table for PowerPivot or Analysis Services in SQL Server
This blog includes the SQL script to generate a table of calendar dates for linking to from within PowerPivot or an Analysis Services tabular model.

Posted by Andy Brown on 04 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

SQL Server Calendar Table for PowerPivot or SSAS Tabular

If you're using PowerPivot or Analysis Services (tabular model), the chances are that you'll need a table of dates to link to.  Something like this:

SQL table of dates

Some of the columns you'd want in a SQL Server calendar table for PowerPivot or a tabular model in Analysis Services.

You could create this table in Excel or Access, but it's easier to have everything in SQL Server. 

If you're using Analysis Services (multi-dimensional model) you can use the built-in calendar generation tool.

SQL Script to create the above table

As a service to the world (and not for redistribution or copying), here is a stored procedure to generate the above table for any two dates:

CREATE PROC spCreateCalendarTable(

@StartDate datetime = '20000101',

@EndDate datetime = '20201231'

) AS

 

-- create a table of dates for use in PowerPivot or SSAS Tabular Models

 

-- NOT FOR COMMERCIAL USE OR REDISTRIBUTION

-- WITHOUT PRIOR WRITTEN PERMISSION FROM WISE OWL

 

-- get rid of any old versions of table

BEGIN TRY

DROP TABLE tblCalendar

END TRY

 

BEGIN CATCH

END CATCH

 

-- first create the table of dates

CREATE TABLE tblCalendar(

[Date] datetime PRIMARY KEY,

[Year] int,

MonthNumber int,

[MonthName] varchar(10),

MonthNameSorted varchar(20),

DayNumber int,

[DayName] varchar(10),

[Quarter] char(2)

)

 

-- now add one date at a time

DECLARE @i int = 0

DECLARE @curdate datetime = @StartDate

 

WHILE @curdate <=>

BEGIN

 

-- add a record for this date (could use FORMAT

-- function if SQL Server 2012 or later)

INSERT INTO tblCalendar (

[Date],

[Year],

MonthNumber,

[MonthName],

MonthNameSorted,

DayNumber,

[DayName],

[Quarter]

) VALUES (

@curdate,

Year(@curdate),

Month(@curdate),

DateName(m,@curdate),

 

-- get month name as eg "01 January" or "11 November"

CASE

WHEN month(@curdate) < 10="">THEN '0'

ELSE ''

END +

CAST(month(@curdate) AS varchar(2)) +

' ' + DateName(m,@curdate),

 

Day(@curdate),

DateName(weekday,@curdate),

 

-- the quarter number

'Q' + CAST(floor((month(@curdate)+2)/3) AS char(1))

)

 

-- increase iteration count and current date

SET @i += 1

SET @curdate = DateAdd(day,1,@curdate)

 

-- quick check we haven't got a ridiculous loop

IF @i > 36600

BEGIN

SELECT 'More than 100 years!'

RETURN

END

END

If you run this it should create a table called tblCalendar in your current database.  You could test it out like this:

-- try this out for 2013 dates

spCreateCalendarTable '20130101', '20131231'

 

-- see if it worked

SELECT * FROM tblCalendar

Obviously you might like to tweak this by adding additional columns, such as the current timesheet week, fiscal year, etc.

This blog has 0 threads Add post