Since we're sharing our Date Dimensions...
I did my latest incarnation of it mostly using derived columns. So far performance seems to have improved over when I was using all typed columns. We've got combined Canadian and US businesses working on the same data, so that's why there's a US and CA values.
It's populated using Jeff Moden's Tally table to generate Calendar_dates, the other values are imparted by loading data from OLTP managed calendar tables to indicate holidays and partial holidays.
CREATE TABLE [dim_calendar](
[Calendar_Key] [int] IDENTITY(1,1) NOT NULL,
[Calendar_Date] [date] NOT NULL,
[US_Holiday_Indicator] [varchar](10) NOT NULL,
[CA_Holiday_Indicator] [varchar](10) NOT NULL,
[US_Working_Days_In_Month] [int] NOT NULL,
[CA_Working_Days_In_Month] [int] NOT NULL,
[US_Working_Hours_In_Day] [int] NOT NULL,
[CA_Working_Hours_In_Day] [int] NOT NULL,
[Week_Ending_Date] AS (dateadd(day,(7)-datepart(weekday,[Calendar_Date]),[Calendar_date])),
[Year_Numeric] AS (datepart(year,[calendar_date])),
[Year_Text] AS (datename(year,[Calendar_Date])),
[Quarter_Numeric] AS (datepart(quarter,[Calendar_Date])),
[Quarter_Smart_key] AS (datepart(year,[Calendar_Date])*(100)+datepart(quarter,[Calendar_date])),
[Quarter_Text_QQYYYY] AS ((('Q'+datename(quarter,[Calendar_Date]))+' ')+datename(year,[Calendar_Date])),
[Quarter_Text_YYYYQQ] AS ((datename(year,[Calendar_Date])+' Q')+datename(quarter,[Calendar_Date])),
[Month_Numeric] AS (datepart(month,[Calendar_Date])),
[Month_Smart_Key] AS (datepart(year,[Calendar_Date])*(100)+datepart(month,[Calendar_Date])),
[Month_Text] AS ((datename(month,[Calendar_Date])+' ')+datename(year,[CAlendar_Date])),
[Day_Numeric] AS (datepart(day,[Calendar_Date])),
[Day_Smart_Key] AS (((datepart(year,[Calendar_Date])*(1000000)+datepart(quarter,[Calendar_Date])*(10000))+datepart(month,[Calendar_Date])*(100))+datepart(day,[Calendar_Date])),
[Day_In_Week] AS (datepart(weekday,[Calendar_Date])),
[Day_Text] AS ((((datename(month,[Calendar_Date])+' ')+datename(day,[Calendar_Date]))+', ')+datename(year,[Calendar_Date])),
[Day_Name] AS (datename(weekday,[Calendar_Date])),
[Day_Type] AS (case when datename(weekday,[Calendar_Date])='Sunday' OR datename(weekday,[Calendar_Date])='Saturday' then 'Weekend' else 'Weekday' end),
[Thirty_Day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(30) else datediff(day,[Calendar_Date],getdate())/(30) end),
[Thirty_Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(30)),(0))+' 30 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(30) when (0) then 'Current 30 Day Period' when (1) then 'Prior 30 Day Period' when (2) then 'Tertiary 30 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(30),(0))+' 30 Day Periods Ago' end end),
[Ninety_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(90) else datediff(day,[Calendar_Date],getdate())/(90) end),
[Ninety_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(90)),(0))+' 90 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(90) when (0) then 'Current 90 Day Period' when (1) then 'Prior 90 Day Period' when (2) then 'Tertiary 90 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(90),(0))+' 90 Day Periods Ago' end end),
[Threehundredsixtyfive_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(365) else datediff(day,[Calendar_Date],getdate())/(365) end),
[Threehundredsixtyfive_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(365)),(0))+' 365 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(365) when (0) then 'Current 365 Day Period' when (1) then 'Prior 365 Day Period' when (2) then 'Tertiary 365 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(365),(0))+' 365 Day Periods Ago' end end),
[Six_Month_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(month,[Calendar_Date],getdate())/(6) else datediff(month,[Calendar_Date],getdate())/(6) end),
[Six_Month_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(month,[Calendar_Date],getdate())/(6)),(0))+' 6 Month Period(s) in the Future' else case datediff(month,[Calendar_Date],getdate())/(6) when (0) then 'Current 6 Month Period' when (1) then 'Prior 6 Month Period' when (2) then 'Tertiary 6 Month Period' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate())/(6),(0))+' 6 Month Periods Ago' end end),
[Quarters_Periods] AS (datediff(quarter,[Calendar_Date],getdate())),
[Quarters_Periods_Text] AS (case when datediff(quarter,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(quarter,[Calendar_Date],getdate())),(0))+' Quarter(s) in the future' else case datediff(quarter,[Calendar_Date],getdate()) when (0) then 'Current Quarter' when (1) then 'Prior Quarter' when (2) then 'Tertiary Quarter' else CONVERT([varchar],datediff(quarter,[Calendar_Date],getdate()),(0))+' Quarters Ago' end end),
[Months_Periods] AS (datediff(month,[Calendar_Date],getdate())),
[Months_Periods_Text] AS (case when datediff(month,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(month,[Calendar_Date],getdate())),(0))+' Month(s) in the Future' else case datediff(month,[Calendar_Date],getdate()) when (0) then 'Current Month' when (1) then 'Prior Month' when (2) then 'Tertiary Month' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate()),(0))+' Months Ago' end end),
[Day_Periods] AS (datediff(day,[Calendar_Date],getdate())),
[Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then case datediff(day,[Calendar_Date],getdate()) when (-1) then 'Tomorrow' else CONVERT([varchar],abs(datediff(day,[Calendar_Date],getdate())),(0))+' Days in the future' end when datediff(day,[Calendar_Date],getdate())=(0) then 'Today' when datediff(day,[Calendar_Date],getdate())=(1) then 'Yesterday' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate()),(0))+' Days Ago' end),
[Week_Periods] AS (datediff(week,[Calendar_Date],getdate())),
[Week_Periods_Text] AS (case when datediff(week,[Calendar_Date],getdate())<(0) then case datediff(week,[Calendar_Date],getdate()) when (-1) then 'Next Week' else CONVERT([varchar],abs(datediff(week,[Calendar_Date],getdate())),(0))+' Weeks in the future' end when datediff(week,[Calendar_Date],getdate())=(0) then 'This Week' when datediff(week,[Calendar_Date],getdate())=(1) then 'Last Week' else CONVERT([varchar],datediff(week,[Calendar_Date],getdate()),(0))+' Weeks Ago' end),
[Days_In_Month] AS (datediff(day,dateadd(month,datediff(month,(0),[Calendar_Date]),(0)),dateadd(month,(1)+datediff(month,(0),[Calendar_Date]),(0)))),
CONSTRAINT [PK__dim_cale__3C52D19446486B8E] PRIMARY KEY CLUSTERED
(
[Calendar_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]