BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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 <= @EndDate
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.