I fill in the calendar table with a dump from an 11,000 integer tally table. It reaches out about 30 years. I have a process that checks nightly if the last date in the calendar table is within 5 years. When that happens, it adds another 11,000 rows.

Why 11,000? Because Jeff Moden's Tally table has 11,000 integers in it... and I just use that for the basic population.

My calendar table started as a dimension in my datawarehouse. I have a lot more information in it than just the date and if it's a working day.

I have reasons why mine is structured as it is (for instance instead of putting a 1 or a 0 in the working days, because it's a dimension I put 'Working Day' or 'Non-Working Day'. Mostly we work with the number of working hours in a day, not counting individual days, so the Holiday/Non-Holiday approach is fine for us as our calculations are based off of the working hours in a day columns.

but it looks like this. Most of the work happens with the actual value columns, the computed columns are there so that when people build reports they can set them to automatically run for today, yesterday, last week, etc and the computed columns will get the right dates.

CREATE TABLE [dbo].[dim_calendar](

[Calendar_Key] [int] IDENTITY(1,1) NOT NULL,

[Calendar_Date] [date] NOT NULL,

[US_Holiday_Indicator] [varchar](15) NULL,

[CA_Holiday_Indicator] [varchar](15) 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)))),

[day_number_in_month] AS (datepart(day,[calendar_date])),

[Quarter_text] AS ('Q'+datename(quarter,[Calendar_Date])),

[Month_name] AS (datename(month,[Calendar_Date])),

[Day_number_text] AS (datename(day,[calendar_date])),

[Year_period_numeric] AS (datediff(year,[Calendar_Date],getdate())),

[Year_period_text] AS (case datediff(year,[Calendar_Date],getdate()) when (0) then 'Current Year' when (1) then 'Last Year' when (2) then 'Prior Year' when (3) then 'Tertiary Year' when (-1) then 'Next Year' else case when datediff(year,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(year,[Calendar_Date],getdate())),0)+' Years From Now' else CONVERT([varchar],abs(datediff(year,[Calendar_Date],getdate())),0)+' Years Ago' end end),

[month_end] AS (CONVERT([date],dateadd(day,(-1),dateadd(month,(1)+datediff(month,(0),[calendar_date]),(0))),0)),

[day_number_in_30_day_periods] AS (case when datediff(day,[Calendar_Date],getdate())>=(0) then (30)-datediff(day,[calendar_date],getdate())%(30) else case datediff(day,[calendar_date],getdate())%(30) when (0) then (30) else abs(datediff(day,[calendar_date],getdate())%(30)) end end),

[day_number_in_90_day_periods] AS (case when datediff(day,[calendar_date],getdate())>=(0) then (90)-datediff(day,[calendar_date],getdate()) else case datediff(day,[calendar_date],getdate())%(90) when (0) then (90) else abs(datediff(day,[calendar_date],getdate())%(90)) end end),

[day_number_in_365_day_periods] AS (case when datediff(day,[calendar_date],getdate())>=(0) then (365)-datediff(day,[calendar_date],getdate()) else case datediff(day,[calendar_date],getdate())%(365) when (0) then (365) else abs(datediff(day,[calendar_date],getdate())%(365)) end end),

[day_number_in_quarter] AS ((1)+datediff(day,CONVERT([date],((CONVERT([varchar],datepart(year,[calendar_date]),(0))+'-')+CONVERT([varchar],datepart(quarter,[calendar_date])*(3)-(2),(0)))+'-01',(0)),[calendar_date])),

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]