Dave Bunch from SQLServerCentral.com T-SQL script that creates a Date-Dimension and populates it for any date-range desired. Includes columns specific to auto-dealerships which can be re-purposed and/or omitted: CurrentBusDay SalesHoliday SalesHolidayName ServiceHoliday ServiceHolidayName TotSalesBusDay TotServiceBusDay Also includes DaylightSavings_flag column (code for which was hard to find). Special Kudos to jhadden's 'Federal Holiday Function' post: https://www.sqlservercentral.com/Forums/1248417/Federal-Holiday-Function. USE --<database-name> GO /****** DIM_DATE Create table section ******/ SET ANSI_NULLS ON GO --DROP TABLE dbo.Dim_Date SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sysobjects WHERE name= N'Dim_Date' AND XTYPE='U') BEGIN CREATE TABLE dbo.Dim_Date ( DateKey INT NOT NULL ,DateValue DATE NULL ,YearDayNumber INT NULL ,QuarterDayNumber INT NULL ,MonthDayNumber INT NULL ,YearMonthNumber INT NULL ,MonthDays INT NULL ,YearQuarterNumber INT NULL ,QuarterDays INT NULL ,YearNumber INT NULL ,PreviousYear INT NULL ,PreviousQuarter INT NULL ,PreviousMonth INT NULL ,WeekDayNumber INT NULL ,[DayName] VARCHAR(10) NULL ,YearWeekNumber INT NULL ,[MonthName] VARCHAR(10) NULL ,YearMonth VARCHAR(8) NULL ,WeekFirstDay DATE NULL ,WeekLastDay DATE NULL ,MonthFirstDay DATE NULL ,MonthLastDay DATE NULL ,QuarterFirstDay DATE NULL ,QuarterLastDay DATE NULL ,PrevMonthFirstDay DATE NULL ,PrevMonthLastDay DATE NULL ,NextMonthFirstDay DATE NULL ,NextMonthLastDay DATE NULL ,DaylightSavings_flag BIT NULL ,MonthDayOccurrence BIGINT NULL ,CurrentBusDay INT NULL ,SalesHoliday INT NOT NULL ,SalesHolidayName VARCHAR(16) NOT NULL ,ServiceHoliday INT NOT NULL ,ServiceHolidayName VARCHAR(16) NOT NULL ,TotSalesBusDay INT NULL ,TotServiceBusDay INT NULL ) ON [PRIMARY] CREATE UNIQUE CLUSTERED INDEX CDX_01_Dim_Date ON dbo.Dim_Date ( DateKey ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END GO /****** Populate DIM_DATE: date-range between @StartDate & @CutoffDate ******/ DECLARE @StartDate DATE = '1990-01-01' -- Start-date DECLARE @CutoffDate DATE = '2036-01-01' -- End-Date IF(OBJECT_ID('tempdb..#Date_Dim') IS NOT NULL) BEGIN DROP TABLE #Date_Dim END IF(OBJECT_ID('tempdb..#Date_Dim2') IS NOT NULL) BEGIN DROP TABLE #Date_Dim2 END IF(OBJECT_ID('tempdb..#DimDate') IS NOT NULL) BEGIN DROP TABLE #DimDate END SELECT CAST(CONVERT(VARCHAR(8),DateTrunc,112) AS INT) AS "DateKey" ,DateTrunc AS "DateValue" ,DATEPART(dy, DateTrunc) AS "YearDayNumber" ,DATEDIFF(dd,MIN(DateTrunc) OVER (PARTITION BY DATEPART(yy, DateTrunc), DATEPART(q, DateTrunc)),DateTrunc)+1 AS "QuarterDayNumber" ,DATEPART(d, DateTrunc) AS "MonthDayNumber" ,DATEPART(m, DateTrunc) AS "YearMonthNumber" ,DAY(EOMONTH(DateTrunc)) AS "MonthDays" ,DATEPART(q, DateTrunc) AS "YearQuarterNumber" ,DATEDIFF(dd,MIN(DateTrunc) OVER (PARTITION BY DATEPART(yy, DateTrunc), DATEPART(q, DateTrunc)) ,MAX(DateTrunc) OVER (PARTITION BY DATEPART(yy, DateTrunc), DATEPART(q, DateTrunc)) )+1 AS "QuarterDays" ,DATEPART(yy, DateTrunc) AS "YearNumber" ,DATEPART(yy, DateTrunc) - 1 AS "PreviousYear" ,DATEPART(qq, DATEADD(month, -3, DateTrunc)) AS "PreviousQuarter" ,DATEPART(m, DATEADD(month, -1, DateTrunc)) AS "PreviousMonth" ,DATEPART(dw, DateTrunc) AS "WeekDayNumber" ,CAST(DATENAME(dw, DateTrunc) AS VARCHAR(10)) AS "DayName" ,DATEPART(ww, DateTrunc) AS "YearWeekNumber" ,CAST(DATENAME(m, DateTrunc) AS VARCHAR(10)) AS "MonthName" ,CAST(CONVERT(VARCHAR(7),DateTrunc,20) AS VARCHAR(8)) AS "YearMonth" ,DATEADD(dd, -(DATEPART(dw, DateTrunc)-1), DateTrunc) AS "WeekFirstDay" ,DATEADD(dd, 7-(DATEPART(dw, DateTrunc)), DateTrunc) AS "WeekLastDay" ,DATEADD(d, 1, EOMONTH(DATEADD(m, -1, DateTrunc))) AS "MonthFirstDay" ,EOMONTH(DateTrunc) AS "MonthLastDay" ,MIN(DateTrunc) OVER (PARTITION BY DATEPART(yy, DateTrunc), DATEPART(q, DateTrunc)) AS "QuarterFirstDay" ,MAX(DateTrunc) OVER (PARTITION BY DATEPART(yy, DateTrunc), DATEPART(q, DateTrunc)) AS "QuarterLastDay" ,DATEADD(d, 1, EOMONTH(DATEADD(m, -2, DateTrunc))) AS "PrevMonthFirstDay" ,EOMONTH(DATEADD(m, -1, DateTrunc)) AS "PrevMonthLastDay" ,DATEADD(d, 1, EOMONTH(DateTrunc)) AS "NextMonthFirstDay" ,EOMONTH(DATEADD(m, 1, DateTrunc)) AS "NextMonthLastDay" ,CAST(CASE WHEN DateTrunc BETWEEN DATEADD(dd,7 + (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(DateTrunc)-1900) * 12 + 2,0))%7)),DATEADD(mm,(YEAR(DateTrunc)-1900) * 12 + 2,0)) AND DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(DateTrunc)-1900) * 12 + 10,0))%7)),DATEADD(mm,(YEAR(DateTrunc)-1900) * 12 + 10,0)) THEN 1 ELSE 0 END AS BIT) AS "DaylightSavings_flag" ,ROW_NUMBER () OVER (PARTITION BY CAST(CONVERT(VARCHAR(7),DateTrunc,20) AS VARCHAR(8)) ,DATENAME(dw, DateTrunc) ORDER BY DateTrunc) AS "MonthDayOccurrence" INTO #Date_Dim FROM (SELECT DateTrunc = DATEADD(DAY, rn - 1, @StartDate) FROM (SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ORDER BY s1.[object_id]) AS x) AS y; SELECT T1.DateKey ,T1.DateValue ,T1.YearDayNumber ,T1.QuarterDayNumber ,T1.MonthDayNumber ,T1.YearMonthNumber ,T1.MonthDays ,T1.YearQuarterNumber ,T1.QuarterDays ,T1.YearNumber ,T1.PreviousYear ,T1.PreviousQuarter ,T1.PreviousMonth ,T1.WeekDayNumber ,T1.[DayName] ,T1.YearWeekNumber ,T1.[MonthName] ,T1.YearMonth ,T1.WeekFirstDay ,T1.WeekLastDay ,T1.MonthFirstDay ,T1.MonthLastDay ,T1.QuarterFirstDay ,T1.QuarterLastDay ,T1.PrevMonthFirstDay ,T1.PrevMonthLastDay ,T1.NextMonthFirstDay ,T1.NextMonthLastDay ,T1.DaylightSavings_flag ,T1.MonthDayOccurrence ,CASE -- Closed Sundays WHEN T1.WeekDayNumber = 1 THEN 0 -- New Year's Day WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 1) THEN 0 -- Independence Day WHEN (T1.YearMonthNumber = 7 AND T1.MonthDayNumber = 4) THEN 0 -- Thanksgiving Day (4th Thursday of November) WHEN (T1.YearMonthNumber = 11 AND T1.MonthDayNumber BETWEEN 22 AND 28 AND T1.WeekDayNumber = 5) THEN 0 -- Xmas Eve (December 24th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 24) THEN 0 -- Xmas Day (December 25th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 25) THEN 0 ELSE 1 END AS "SalesBusDay" ,CASE -- New Year's Day WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 1) THEN 1 -- Independence Day WHEN (T1.YearMonthNumber = 7 AND T1.MonthDayNumber = 4) THEN 1 -- Thanksgiving Day (4th Thursday of November) WHEN (T1.YearMonthNumber = 11 AND T1.MonthDayNumber BETWEEN 22 AND 28 AND T1.WeekDayNumber = 5) THEN 1 -- Xmas Eve (December 24th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 24) THEN 1 -- Xmas Day (December 25th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 25) THEN 1 ELSE 0 END AS "SalesHoliday" ,CASE -- New Year's Day WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 1) THEN 'New Year''s Day' -- Independence Day WHEN (T1.YearMonthNumber = 7 AND T1.MonthDayNumber = 4) THEN 'Independence Day' -- Thanksgiving Day (4th Thursday of November) WHEN (T1.YearMonthNumber = 11 AND T1.MonthDayNumber BETWEEN 22 AND 28 AND T1.WeekDayNumber = 5) THEN 'Thanksgiving Day' -- Xmas Eve (December 24th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 24) THEN 'Xmas Eve' -- Xmas Day (December 25th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 25) THEN 'Xmas Day' ELSE '' END AS "SalesHolidayName" ,CASE -- Closed Sundays WHEN T1.WeekDayNumber = 1 THEN 0 -- New Year's Day WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 1) THEN 0 -- Falls on Sunday WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 2 AND T1.WeekDayNumber = 2) THEN 0 -- Independence Day WHEN (T1.YearMonthNumber = 7 AND T1.MonthDayNumber = 4) THEN 0 -- Thanksgiving Day (4th Thursday of November) WHEN (T1.YearMonthNumber = 11 AND T1.MonthDayNumber BETWEEN 22 AND 28 AND T1.WeekDayNumber = 5) THEN 0 -- Xmas Eve (December 24th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 24) THEN 0 -- Xmas Day (December 25th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 25) THEN 0 -- Falls on Sunday WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 26 AND T1.WeekDayNumber = 2) THEN 0 ELSE 1 END AS "ServiceBusDay" ,CASE -- New Year's Day WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 1) THEN 1 -- Falls on Sunday WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 2 AND T1.WeekDayNumber = 2) THEN 1 -- Independence Day WHEN (T1.YearMonthNumber = 7 AND T1.MonthDayNumber = 4) THEN 1 -- Thanksgiving Day (4th Thursday of November) WHEN (T1.YearMonthNumber = 11 AND T1.MonthDayNumber BETWEEN 22 AND 28 AND T1.WeekDayNumber = 5) THEN 1 -- Xmas Eve (December 24th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 24) THEN 1 -- Xmas Day (December 25th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 25) THEN 1 -- Falls on Sunday WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 26 AND T1.WeekDayNumber = 2) THEN 1 ELSE 0 END AS "ServiceHoliday" ,CASE -- New Year's Day WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 1) THEN 'New Year''s Day' -- Falls on Sunday WHEN (T1.YearMonthNumber = 1 AND T1.MonthDayNumber = 2 AND T1.WeekDayNumber = 2) THEN 'New Year''s Day' -- Independence Day WHEN (T1.YearMonthNumber = 7 AND T1.MonthDayNumber = 4) THEN 'Independence Day' -- Thanksgiving Day (4th Thursday of November) WHEN (T1.YearMonthNumber = 11 AND T1.MonthDayNumber BETWEEN 22 AND 28 AND T1.WeekDayNumber = 5) THEN 'Thanksgiving Day' -- Xmas Eve (December 24th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 24) THEN 'Xmas Eve' -- Xmas Day (December 25th) WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 25) THEN 'Xmas Day' -- Falls on Sunday WHEN (T1.YearMonthNumber = 12 AND T1.MonthDayNumber = 26 AND T1.WeekDayNumber = 2) THEN 'Xmas Day' ELSE '' END AS "ServiceHolidayName" INTO #Date_Dim2 FROM #Date_Dim T1 ORDER BY 1 SELECT T1.DateKey ,T1.DateValue ,T1.YearDayNumber ,T1.QuarterDayNumber ,T1.MonthDayNumber ,T1.YearMonthNumber ,T1.MonthDays ,T1.YearQuarterNumber ,T1.QuarterDays ,T1.YearNumber ,T1.PreviousYear ,T1.PreviousQuarter ,T1.PreviousMonth ,T1.WeekDayNumber ,T1.[DayName] ,T1.YearWeekNumber ,T1.[MonthName] ,T1.YearMonth ,T1.WeekFirstDay ,T1.WeekLastDay ,T1.MonthFirstDay ,T1.MonthLastDay ,T1.QuarterFirstDay ,T1.QuarterLastDay ,T1.PrevMonthFirstDay ,T1.PrevMonthLastDay ,T1.NextMonthFirstDay ,T1.NextMonthLastDay ,T1.DaylightSavings_flag ,T1.MonthDayOccurrence ,SUM(T1.SalesBusDay) OVER (PARTITION BY T1.YearMonth ORDER BY T1.DateValue) AS CurrentBusDay -- ,T1.SalesBusDay ,T1.SalesHoliday ,T1.SalesHolidayName -- ,T1.ServiceBusDay ,T1.ServiceHoliday ,T1.ServiceHolidayName ,T2.TotSalesBusDay ,T2.TotServiceBusDay INTO #DimDate FROM #Date_Dim2 T1 INNER JOIN (SELECT YearMonthNumber ,MonthDays ,YearNumber ,MonthName ,YearMonth ,SUM(SalesBusDay) AS TotSalesBusDay ,SUM(ServiceBusDay) AS TotServiceBusDay FROM #Date_Dim2 GROUP BY YearMonthNumber ,MonthDays ,YearNumber ,MonthName ,YearMonth) T2 ON T2.YearMonth = T1.YearMonth ORDER BY 1 USE --<database-name> GO INSERT INTO dbo.Dim_Date (DateKey ,DateValue ,YearDayNumber ,QuarterDayNumber ,MonthDayNumber ,YearMonthNumber ,MonthDays ,YearQuarterNumber ,QuarterDays ,YearNumber ,PreviousYear ,PreviousQuarter ,PreviousMonth ,WeekDayNumber ,[DayName] ,YearWeekNumber ,[MonthName] ,YearMonth ,WeekFirstDay ,WeekLastDay ,MonthFirstDay ,MonthLastDay ,QuarterFirstDay ,QuarterLastDay ,PrevMonthFirstDay ,PrevMonthLastDay ,NextMonthFirstDay ,NextMonthLastDay ,DaylightSavings_flag ,MonthDayOccurrence ,CurrentBusDay ,SalesHoliday ,SalesHolidayName ,ServiceHoliday ,ServiceHolidayName ,TotSalesBusDay ,TotServiceBusDay) SELECT S.* FROM #DimDate S LEFT OUTER JOIN dbo.Dim_Date T (NOLOCK) ON T.DateKey = S.DateKey WHERE T.DateKey IS NULL ORDER BY 1; More » |