Create a Calendar Dimension using T-SQL

[FerencMantfeld]

I had the situation where I needed a calendar dimension table 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.

Acknowledgements: Ali Farahani 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, 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 )
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, MonthName, MonthShortName , MNTH, YR, 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)

END

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