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 --Fiscal year varies between countries
,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