Create Table script for a Date Dimension

USE [SS64_DATA_WAREHOUSE]
GO

IF OBJECT_ID('dimDates') IS NOT NULL
	DROP TABLE dimDates
GO

CREATE TABLE dbo.dimDates (
 DateKey AS Convert(int, Convert(char(8), ActualDate, 112)) PERSISTED NOT NULL
,ActualDate DATETIME NOT NULL
,YYYYMMDD As Year(ActualDate) * 10000 + Month(ActualDate) * 100 + DAY(ActualDate) PERSISTED NOT NULL
,YYYY AS YEAR(ActualDate) PERSISTED NOT NULL
,CalendarYear AS Convert(char(4), YEAR(ActualDate)) PERSISTED NOT NULL
,CalendarYearNumber AS (DatePart(year,[ActualDate])) PERSISTED NOT NULL
,YearStart AS DateAdd(year,DATEDIFF(year,0,ActualDate),0) PERSISTED NOT NULL
,FinancialYYYY AS YEAR(DateAdd(month,6,ActualDate)) PERSISTED NOT NULL --Varies between countries see http://en.wikipedia.org/wiki/Fiscal_year
,FinancialYear AS Convert(char(4),YEAR(DateAdd(month,6,ActualDate))-1) + '/' + RIGHT(Convert(char(4),Year(DateAdd(month,6,ActualDate))),2) PERSISTED NOT NULL
,FinancialYearStart AS DateAdd(month,-6,DateAdd(year,DATEDIFF(year,0,DateAdd(Month,6,ActualDate)),0)) PERSISTED NOT NULL
,MonthKey AS Convert(int, Convert(char(6),ActualDate,112)) PERSISTED NOT NULL
,MonthName AS CASE Month(ActualDate)
                 WHEN 1 then 'Jan'
                 WHEN 2 then 'Feb'
                 WHEN 3 then 'Mar'
                 WHEN 4 then 'Apr'
                 WHEN 5 then 'May'
                 WHEN 6 then 'Jun'
                 WHEN 7 then 'Jul'
                 WHEN 8 then 'Aug'
                 WHEN 9 then 'Sep'
                 WHEN 10 then 'Oct'
                 WHEN 11 then 'Nov'
                 WHEN 12 then 'Dec'
               END
                  + ' ' + Convert(char(4), YEAR(ActualDate)) PERSISTED NOT NULL
,FrenchMonthName AS CASE MONTH(ActualDate)
                 WHEN 1 THEN 'janv'
                 WHEN 2 THEN 'févr'
                 WHEN 3 THEN 'mars'
                 WHEN 4 THEN 'avr'
                 WHEN 5 THEN 'mai'
                 WHEN 6 THEN 'juin'
                 WHEN 7 THEN 'juil'
                 WHEN 8 THEN 'Août'
                 WHEN 9 THEN 'sept'
                 WHEN 10 THEN 'oct'
                 WHEN 11 THEN 'nov'
                 WHEN 12 THEN 'Déc'
               END
                  + ' ' + Convert(char(4), YEAR(ActualDate)) PERSISTED NOT NULL
, [QuarterNumber] AS (DatePart(quarter,[ActualDate]))PERSISTED NOT NULL
, [QuarterName] AS ('QTR'+Convert([varchar](1),DatePart(quarter,[ActualDate]),(0))) PERSISTED NOT NULL
, [MonthNumber] AS (DatePart(month,[ActualDate])) PERSISTED NOT NULL
, [DayOfWeek] AS CASE (datediff(dd,dateadd(dd,(datediff(dd,convert(DATETIME,-53690+((7+5)%7),112),[ActualDate])/7)*7,convert(DATETIME,-53690+((7+5)%7),112)), [ActualDate])
) --We avoid using DatePart/weekday here so that the result is deterministic and can be persisted.
                 WHEN 1 THEN 'Sunday'
                 WHEN 2 THEN 'Monday'
                 WHEN 3 THEN 'Tuesday'
                 WHEN 4 THEN 'Wednesday'
                 WHEN 5 THEN 'Thursday'
                 WHEN 6 THEN 'Friday'
                 WHEN 0 THEN 'Saturday'
End PERSISTED NOT NULL
, [CalendarDayNumberOfMonth] AS (DatePart(day,[ActualDate])) PERSISTED NOT NULL
, [FirstDayofMonth] as DateAdd(mm, DateDiff(mm,0,[ActualDate]), 0) PERSISTED NOT NULL
, [LastDayofMonth] as DateAdd(ms,-3,DateAdd(mm, DATEDIFF(m,0,[ActualDate]  )+1, 0)) PERSISTED NOT NULL
);
GO

“I don't understand how a woman can leave the house without fixing herself up a little - if only out of politeness. And then, you never know, maybe that's the day she has a date with destiny. And it's best to be as pretty as possible for destiny” - Coco Chanel

Related

Date Dimension


© Copyright SS64.com 1999-2014
Some rights reserved