question

I am building a date dimension for a data warehouse. I've got almost all of the columns I think I need but I can imagine that users may want to search for the first or last business/working day of a period (week, month, calendar year, calendar quarter, fiscal quarter). My date dimension already calculates holidays. I've supplemented it with a list of business closure days as well (e.g. if a holiday falls on a weekend day the "in lieu of" day has an indicator for the day. Here's what I have so far. If you wanted to run this query you'd have to build yourself some sort of table which has business some business closure days in it. Mine is in a table called R_AER_Office_Closure which has a date column and a closure day name column Thanks in advance for any assistance you can provide IF OBJECT_ID(N'TEMPDB..##T1') IS NOT NULL DROP TABLE ##T1 DECLARE @Start_Date AS DATE = '1850-01-01' DECLARE @End_Date AS DATE = '2050-12-31' ;WITH [Calendar_Dimension] AS ( SELECT @Start_Date AS [Date] ,DATEPART(YEAR, @Start_Date) AS [Year] ,DATEPART(QUARTER, @Start_Date) AS [Calendar_Quarter] ,CASE WHEN DATEPART(MONTH, @Start_Date) BETWEEN 4 AND 6 THEN 1 WHEN DATEPART(MONTH, @Start_Date) BETWEEN 7 AND 9 THEN 2 WHEN DATEPART(MONTH, @Start_Date) BETWEEN 10 AND 12 THEN 3 ELSE 4 END AS [Fiscal_Quarter] ,DATEPART(MONTH, @Start_Date) AS [Month] ,DATEPART(DAYOFYEAR, @Start_Date) AS [Day_Of_Year] ,DATEPART(DAY, @Start_Date) AS [Day] ,DATEPART(WEEK, @Start_Date) AS [Week] ,DATEPART(WEEKDAY, @Start_Date) AS [Weekday] ,CASE WHEN DATEPART(DAY, @Start_Date) BETWEEN 1 AND 7 THEN 1 WHEN DATEPART(DAY, @Start_Date) BETWEEN 8 AND 14 THEN 2 WHEN DATEPART(DAY, @Start_Date) BETWEEN 15 AND 21 THEN 3 WHEN DATEPART(DAY, @Start_Date) BETWEEN 22 AND 28 THEN 4 ELSE 5 END AS [Weekday_Of_Month] ,DATENAME(MONTH, @Start_Date) AS [Month_Name] ,DATENAME(WEEKDAY, @Start_Date) AS [Day_Name] ,CASE WHEN DATEPART(QUARTER, @Start_Date) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(QUARTER, @Start_Date) % 10 = 1 THEN 'st' WHEN DATEPART(QUARTER, @Start_Date) % 10 = 2 THEN 'nd' WHEN DATEPART(QUARTER, @Start_Date) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Calendar_Quarter_Suffix] ,CASE WHEN DATEPART(MONTH, @Start_Date) BETWEEN 4 AND 6 THEN 'st' WHEN DATEPART(MONTH, @Start_Date) BETWEEN 7 AND 9 THEN 'nd' WHEN DATEPART(MONTH, @Start_Date) BETWEEN 10 AND 12 THEN 'rd' ELSE 'th' END AS [Fiscal_Quarter_Suffix] ,CASE WHEN DATEPART(DAYOFYEAR, @Start_Date) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(DAYOFYEAR, @Start_Date) % 10 = 1 THEN 'st' WHEN DATEPART(DAYOFYEAR, @Start_Date) % 10 = 2 THEN 'nd' WHEN DATEPART(DAYOFYEAR, @Start_Date) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Day_of_Year_Suffix] ,CASE WHEN DATEPART(DAY, @Start_Date) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(DAY, @Start_Date) % 10 = 1 THEN 'st' WHEN DATEPART(DAY, @Start_Date) % 10 = 2 THEN 'nd' WHEN DATEPART(DAY, @Start_Date) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Day_Suffix] ,CASE WHEN DATEPART(WEEK, @Start_Date) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(WEEK, @Start_Date) % 10 = 1 THEN 'st' WHEN DATEPART(WEEK, @Start_Date) % 10 = 2 THEN 'nd' WHEN DATEPART(WEEK, @Start_Date) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Week_Suffix] ,CASE WHEN DATEPART(WEEKDAY, @Start_Date) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(WEEKDAY, @Start_Date) % 10 = 1 THEN 'st' WHEN DATEPART(WEEKDAY, @Start_Date) % 10 = 2 THEN 'nd' WHEN DATEPART(WEEKDAY, @Start_Date) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Weekday_Suffix] ,CASE WHEN DATEPART(DAY, @Start_Date) BETWEEN 1 AND 7 THEN 'st' WHEN DATEPART(DAY, @Start_Date) BETWEEN 8 AND 14 THEN 'nd' WHEN DATEPART(DAY, @Start_Date) BETWEEN 15 AND 21 THEN 'rd' ELSE 'th' END AS [Weekday_Of_Month_Suffix] ,CASE WHEN DATEPART(WEEKDAY, @Start_Date) IN (7, 1) THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS [Is_Weekday] ,CAST(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date) + 1, 0)) AS DATE) AS [End_of_Month] ,CASE WHEN DAY(DATEADD(DAY,1,@Start_Date)) = 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS [Is_Month_End] ,CAST('Newyear''s day' AS VARCHAR(20)) AS [Alberta_Holiday_Name] UNION ALL SELECT DATEADD(DAY, 1, [Date]) AS [Date] ,DATEPART(YEAR, DATEADD(DAY, 1, [Date])) AS [Year] ,DATEPART(QUARTER, DATEADD(DAY, 1, [Date])) AS [Calendar_Quarter] ,CASE WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) BETWEEN 4 AND 6 THEN 1 WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) BETWEEN 7 AND 9 THEN 2 WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) BETWEEN 10 AND 12 THEN 3 ELSE 4 END AS [Fiscal_Quarter] ,DATEPART(MONTH, DATEADD(DAY, 1, [Date])) AS [Month] ,DATEPART(DAYOFYEAR, DATEADD(DAY, 1, [Date])) AS [Day_Of_Year] ,DATEPART(DAY, DATEADD(DAY, 1, [Date])) AS [Day] ,DATEPART(WEEK, DATEADD(DAY, 1, [Date])) AS [Week] ,DATEPART(WEEKDAY, DATEADD(DAY, 1, [Date])) AS [Weekday] ,CASE WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 1 AND 7 THEN 1 WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 8 AND 14 THEN 2 WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 15 AND 21 THEN 3 WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 22 AND 28 THEN 4 ELSE 5 END AS [Weekday_Of_Month] ,DATENAME(MONTH, DATEADD(DAY, 1, [Date])) AS [Month_Name] ,DATENAME(WEEKDAY, DATEADD(DAY, 1, [Date])) AS [Day_Name] ,CASE WHEN DATEPART(QUARTER, DATEADD(DAY, 1, [Date])) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(QUARTER, DATEADD(DAY, 1, [Date])) % 10 = 1 THEN 'st' WHEN DATEPART(QUARTER, DATEADD(DAY, 1, [Date])) % 10 = 2 THEN 'nd' WHEN DATEPART(QUARTER, DATEADD(DAY, 1, [Date])) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Calendar_Quarter_Suffix] ,CASE WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) BETWEEN 4 AND 6 THEN 'st' WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) BETWEEN 7 AND 9 THEN 'nd' WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) BETWEEN 10 AND 12 THEN 'rd' ELSE 'th' END AS [Fiscal_Quarter] ,CASE WHEN DATEPART(DAYOFYEAR, DATEADD(DAY, 1, [Date])) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(DAYOFYEAR, DATEADD(DAY, 1, [Date])) % 10 = 1 THEN 'st' WHEN DATEPART(DAYOFYEAR, DATEADD(DAY, 1, [Date])) % 10 = 2 THEN 'nd' WHEN DATEPART(DAYOFYEAR, DATEADD(DAY, 1, [Date])) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Day_of_Year_Suffix] ,CASE WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) % 10 = 1 THEN 'st' WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) % 10 = 2 THEN 'nd' WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Day_Suffix] ,CASE WHEN DATEPART(WEEK, DATEADD(DAY, 1, [Date])) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(WEEK, DATEADD(DAY, 1, [Date])) % 10 = 1 THEN 'st' WHEN DATEPART(WEEK, DATEADD(DAY, 1, [Date])) % 10 = 2 THEN 'nd' WHEN DATEPART(WEEK, DATEADD(DAY, 1, [Date])) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Week_Suffix] ,CASE WHEN DATEPART(WEEKDAY, DATEADD(DAY, 1, [Date])) % 100 IN (11,12,13) THEN 'th' --first checks for exception WHEN DATEPART(WEEKDAY, DATEADD(DAY, 1, [Date])) % 10 = 1 THEN 'st' WHEN DATEPART(WEEKDAY, DATEADD(DAY, 1, [Date])) % 10 = 2 THEN 'nd' WHEN DATEPART(WEEKDAY, DATEADD(DAY, 1, [Date])) % 10 = 3 THEN 'rd' ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0) END AS [Weekday_Suffix] ,CASE WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 1 AND 7 THEN 'st' WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 8 AND 14 THEN 'nd' WHEN DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 15 AND 21 THEN 'rd' ELSE 'th' END AS [Weekday_Of_Month_Suffix] ,CASE WHEN DATEPART(WEEKDAY, DATEADD(DAY, 1, [Date])) IN (7, 1) THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS [Is_Weekday] ,CAST(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, 1, [Date])) + 1, 0)) AS DATE) AS [End_of_Month] ,CASE WHEN DAY(DATEADD(DAY,1,DATEADD(DAY, 1, [Date]))) = 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS [Is_Month_End] ,CAST(CASE WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 1 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) = 1 THEN 'Newyear''s day' WHEN DATEPART(YEAR, DATEADD(DAY, 1, [Date])) >= 1990 AND DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 2 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 15 AND 21 AND DATENAME(WEEKDAY, DATEADD(DAY, 1, [Date])) = 'Monday' THEN 'Family day' WHEN DATEADD(DAY, 3, [Date]) = (SELECT [Utilities].[dbo].[fnEasterSunday] (DATEPART(YEAR, DATEADD(DAY, 1, [Date])))) THEN 'Good Friday' WHEN DATEADD(DAY, 1, [Date]) = (SELECT [Utilities].[dbo].[fnEasterSunday] (DATEPART(YEAR, DATEADD(DAY, 1, [Date])))) THEN 'Easter' WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 5 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 18 AND 24 AND DATENAME(WEEKDAY, DATEADD(DAY, 1, [Date])) = 'Monday' THEN 'Victoria day' WHEN DATEPART(YEAR, DATEADD(DAY, 1, [Date])) >= 1867 AND DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 7 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) = 1 THEN 'Canada day' WHEN DATEPART(YEAR, DATEADD(DAY, 1, [Date])) >= 1974 AND DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 8 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 1 AND 7 AND DATENAME(WEEKDAY, DATEADD(DAY, 1, [Date])) = 'Monday' THEN 'Heritage day' WHEN DATEPART(YEAR, DATEADD(DAY, 1, [Date])) >= 1894 AND DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 9 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 1 AND 7 AND DATENAME(WEEKDAY, DATEADD(DAY, 1, [Date])) = 'Monday' THEN 'Labour day' WHEN DATEPART(YEAR, DATEADD(DAY, 1, [Date])) >= 1879 AND DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 10 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) BETWEEN 8 AND 14 AND DATENAME(WEEKDAY, DATEADD(DAY, 1, [Date])) = 'Monday' THEN 'Thanksgiving day' WHEN DATEPART(YEAR, DATEADD(DAY, 1, [Date])) >= 1931 AND DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 11 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) = 11 THEN 'Remembrance day' WHEN DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 12 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) = 25 THEN 'Christmas day' WHEN DATEPART(YEAR, DATEADD(DAY, 1, [Date])) >= 1871 AND DATEPART(MONTH, DATEADD(DAY, 1, [Date])) = 12 AND DATEPART(DAY, DATEADD(DAY, 1, [Date])) = 26 THEN 'Boxing day' ELSE 'NA' END AS VARCHAR(20)) AS [Alberta_Holiday_Name] FROM [Calendar_Dimension] WHERE DATEADD(DAY, 1, [Date]) <= @End_Date ) SELECT D.* ,CASE WHEN DATEPART(YEAR, D.[Date]) < 1974 AND C.[Closure_Name] = 'Heritage Day' THEN 'NA' WHEN DATEPART(YEAR, D.[Date]) < 1990 AND C.[Closure_Name] = 'Family Day' THEN 'NA' ELSE ISNULL(C.[Closure_Name], 'NA') END AS [AER_Closure_Name] ,CAST(NULL AS BIT) AS [Is_First_Business_Day_of_Week] ,CAST(NULL AS BIT) AS [Is_First_Business_Day_of_Month] ,CAST(NULL AS BIT) AS [Is_First_Business_Day_of_Calendar_Year] ,CAST(NULL AS BIT) AS [Is_First_Business_Day_of_Calendar_Quarter] ,CAST(NULL AS BIT) AS [Is_First_Business_Day_of_Fiscal_Year] ,CAST(NULL AS BIT) AS [Is_First_Business_Day_of_Fiscal_Quarter] ,CAST(NULL AS BIT) AS [Is_Last_Business_Day_of_Week] ,CAST(NULL AS BIT) AS [Is_Last_Business_Day_of_Month] ,CAST(NULL AS BIT) AS [Is_Last_Business_Day_of_Calendar_Year] ,CAST(NULL AS BIT) AS [Is_Last_Business_Day_of_Calendar_Quarter] ,CAST(NULL AS BIT) AS [Is_Last_Business_Day_of_Fiscal_Year] ,CAST(NULL AS BIT) AS [Is_Last_Business_Day_of_Fiscal_Quarter] INTO ##T1 FROM [Calendar_Dimension] D LEFT JOIN [Utilities].[dbo].[R_AER_Office_Closure] C ON C.[Closure_Date] = D.[Date] OPTION (MAXRECURSION 0)