Sql Server: Create a Calendar dimension in Sql Server using Transact SQL

[FerencMantfeld]

I had the situation where I needed a calendar dimension table in Sql Server so that I could reference date periods, years, quarters, financial period, etc. No table was found in the simplistic data mart, so I landed up creating one.

Acknowledgments: Ali Farahani & Peter Sampson who helped me to get started with this.

This dimension table has 2 possible keys, one is the truncated date (datetime with hours, minutes and seconds set to zero) and the other is a logical sort key using the time format YYYYMMDD.


create table dim_Calendar (
CalDate datetime,
YYYYMMDD char(8),
MonthName varchar(20),
MonthShortName char(3),
MNTH int,
YR int,
FiscalYear int,
FiscalMNTH int,
CalQTR int,
FiscalQTR int,
DayOfWeek int,
WeekDay varchar(20),
WeekDayShort char(3),
CalPeriod varchar(20)); create index dim_calendar_idx1 on dim_calendar(CalDate (http://www.seemoredata.com/en/../wiki/tips:sql:caldate), YYYYMMDD ) ;
This index will aid in lookup, even though there are only just short of 3,000 records in this dataset, if you're looking for the last 10 days, no sense scanning through 3,000 records each time.

Rules: The Australian financial year starts on 1 July. This calendar has to be able to address up to 7 financial years ago and to the end of the current financial year.

code for populating dim_Calendar:

DECLARE @dtStartDate AS SMALLDATETIME DECLARE @dtEndDate AS SMALLDATETIME

SELECT @dtStartDate = ( case when datepart(month,dateadd(year,-7,getdate())) > 6 then


convert(datetime,convert(varchar,DATEPART(YEAR,dat eadd(year,-7,getdate()))) + '0701',112)
else convert(datetime,convert(varchar,DATEPART(YEAR,dat eadd(year,-8,getdate())))) end )/* The above finds the start of the financial year, 7 financial years ago, based on Fiscal year starting 1 July each year */

SELECT @dtEndDate = ( case when substring(convert(varchar,getdate(),112),5,4) > '0701' then


convert(datetime,convert(varchar,datepart(year,get date()) + 1) + '0701',112)
else convert(datetime,convert(varchar,datepart(year,get date())) + '0701',112) end )

WHILE @dtStartDate < @dtEndDate BEGIN INSERT INTO dim_calendar ( YYYYMMDD, CalDate (http://www.seemoredata.com/en/../wiki/tips:sql:caldate), MonthName (http://www.seemoredata.com/en/../wiki/tips:sql:monthname), MonthShortName (http://www.seemoredata.com/en/../wiki/tips:sql:monthshortname) , MNTH, YR, CalPeriod (http://www.seemoredata.com/en/../wiki/tips:sql:calperiod),
DayOfWeek ,WeekDay, WeekDayShort , FiscalYear, FiscalMNTH, CalQTR, FiscalQTR)
SELECT CAST(CONVERT(CHAR(8), @dtStartDate, 112) AS INT)
,CalendarDate = @dtStartDate
,MonthName = datename(month,@dtStartDate)
,MonthShortName = substring( datename(month,@dtStartDate), 1, 3)
,MNTH = datepart(month,@dtStartDate)
,YR = convert(int,substring(convert(varchar,@dtStartDate ,112),1,4))
,CalPeriod = substring(convert(varchar,@dtStartDate,112),1,4) + ' ' + substring( datename(month,@dtStartDate), 1, 3)
,DayOfWeek = datepart(weekday,@dtStartDate)
,WeekDay = datename(weekday,@dtStartDate)
,WeekDayShort = substring(datename(weekday,@dtStartDate),1,3)
,FiscalYear = (case when datepart(month,@dtStartDate) > 6
then convert(int,substring(convert(varchar,@dtStartDate ,112),1,4)) + 1
else convert(int,substring(convert(varchar,@dtStartDate ,112),1,4)) end )
,FiscalMNTH = case when (datepart(month,@dtStartDate) + 6) %12 = 0 then 12 else (datepart(month,@dtStartDate) + 6) %12 end
,CalQTR = (case
when datepart(month,@dtStartDate) < 4 then 1
when datepart(month,@dtStartDate) < 7 then 2
when datepart(month,@dtStartDate) < 10 then 3
else 4 end )
,FiscalQTR = (case
when datepart(month,@dtStartDate) < 4 then 3
when datepart(month,@dtStartDate) < 7 then 4
when datepart(month,@dtStartDate) < 10 then 1
else 2 end )
SET @dtStartDate = DATEADD(day, 1, @dtStartDate) /* Increment the date */
END

The screenshot below shows the Calendar, as queried in Sql Server management studio

http://www.seemoredata.com/en/../wiki/_media/tips:sql:dim_calendar.png (http://www.seemoredata.com/en/../wiki/_detail/tips:sql:dim_calendar.png?id=tips%3Asql%3Adimcal)