So I need to create a date dimension, and I refuse to use RBAR-y loops or cursors to do it.
That means I'm limiting myself, intentionally, to using a tally table (also sometimes called a numbers table).
I found the script by DustinRyan, URL included inline below, that did most of what I needed. However, the Day Of The Week In Month (DoWInMonth) column was filled by a loop, and that was going to violate the requirements for making this dimension.
Also, the election day query was using a nested loop, and that was going to violate the requirements for the making of this dimension.
Found below is my update of his script to use a tally table to dynamically generate the required column. On my development virtual machine, running on a laptop spinning at 5400 RPM, generating and inserting all 300 years of this data as an insert takes less than 5 seconds. Not bad.
Not bad at all.
PRINT convert(VARCHAR, getdate(), 113)
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF object_id(N'tempdb..dimDate') IS NOT NULL
DROP TABLE dimDate;
CREATE TABLE [dbo].[DimDate] (
[DateSK] [int] NOT NULL
, [FullDate] [datetime] NOT NULL
, [Day] [tinyint] NOT NULL
, [DaySuffix] [varchar](4) NOT NULL
, [DayOfWeek] [varchar](9) NOT NULL
, [DayOfWeekNumber] [int] NOT NULL
, [DayOfWeekInMonth] [tinyint] NOT NULL
, [DayOfYearNumber] [int] NOT NULL
, [RelativeDays] INT NOT NULL
, [WeekOfYearNumber] [tinyint] NOT NULL
, [WeekOfMonthNumber] [tinyint] NOT NULL
, [RelativeWeeks] INT NOT NULL
, [CalendarMonthNumber] [tinyint] NOT NULL
, [CalendarMonthName] [varchar](9) NOT NULL
, [RelativeMonths] INT NOT NULL
, [CalendarQuarterNumber] [tinyint] NOT NULL
, [CalendarQuarterName] [varchar](6) NOT NULL
, [RelativeQuarters] INT NOT NULL
, [CalendarYearNumber] INT NOT NULL
, [RelativeYears] INT NOT NULL
, [StandardDate] [varchar](10) NULL
, [WeekDayFlag] BIT NOT NULL
, [HolidayFlag] BIT NOT NULL
, [OpenFlag] BIT NOT NULL
, [FirstDayOfCalendarMonthFlag] BIT NOT NULL
, [LastDayOfCalendarMonthFlag] BIT NOT NULL
, [HolidayText] [varchar](50) NULL CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ([DateSK] ASC) WITH (
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, FILLFACTOR = 100
)
ON [PRIMARY]
) ON [PRIMARY]
GO
DECLARE @CurrentDate DATE = '1/1/1800';
DECLARE @StartDate DATETIME = cast('12/31/1799' AS DATE);
INSERT INTO DimDate (
[DateSK]
,
[FullDate]
, [Day]
, [DaySuffix]
, [DayOfWeek]
, [DayOfWeekNumber]
, [DayOfWeekInMonth]
, [DayOfYearNumber]
, [RelativeDays]
, [WeekOfYearNumber]
, [WeekOfMonthNumber]
, [RelativeWeeks]
, [CalendarMonthNumber]
, [CalendarMonthName]
, [RelativeMonths]
, [CalendarQuarterNumber]
, [CalendarQuarterName]
, [RelativeQuarters]
, [CalendarYearNumber]
, [RelativeYears]
, [StandardDate]
, [WeekDayFlag]
, [HolidayFlag]
, [OpenFlag]
, [FirstDayOfCalendarMonthFlag]
, [LastDayOfCalendarMonthFlag]
)
SELECT CONVERT(VARCHAR, calcdate, 112) AS [DateSK]
, calcDate AS [FullDate]
, DATEPART(DAY, calcDate) AS [Day]
, CASE
WHEN DATEPART(DAY, calcDate) IN (
11
, 12
, 13
)
THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 1
THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 2
THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DAY, calcDate), 1) = 3
THEN CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DAY, calcDate) AS VARCHAR) + 'th'
END AS [DaySuffix]
, CASE DATEPART(DW, CalcDate)
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 7
THEN 'Saturday'
END AS [DayOfWeek]
, DATEPART(DW, CalcDate) AS [DayOfWeekNumber]
, rank() OVER (
PARTITION BY yearno
, monthno
, dow ORDER BY calcdate
) AS DOWInMonth
, DATEPART(dy, CalcDate) AS [DayOfYearNumber]
, DATEDIFF(dd, @CurrentDate, CalcDate) AS [RelativeDays]
, DATEPART(ww, CalcDate) AS [WeekOfYearNumber]
, DATEPART(ww, CalcDate) + 1 - DATEPART(ww, CAST(DATEPART(mm, CalcDate) AS VARCHAR) + '/1/' + CAST(DATEPART(yy, CalcDate) AS VARCHAR)) [WeekOfMonthNumber]
, DATEDIFF(ww, @CurrentDate, CalcDate) AS [RelativeWeeks]
, DATEPART(MONTH, CalcDate) AS [CalendarMonthNumber]
, DATENAME(MONTH, CalcDate) AS [CalendarMonthName]
, DATEDIFF(MONTH, @CurrentDate, CalcDate) AS [RelativeMonths]
, DATEPART(qq, CalcDate) AS [CalendarQuarterNumber]
, CASE DATEPART(qq, CalcDate)
WHEN 1
THEN 'First'
WHEN 2
THEN 'Second'
WHEN 3
THEN 'Third'
WHEN 4
THEN 'Fourth'
END AS [CalendarQuarterName]
, DATEDIFF(qq, @CurrentDate, CalcDate) AS [RelativeQuarters]
, DATEPART(YEAR, CalcDate) AS [CalendarYearNumber]
, DATEDIFF(YEAR, @CurrentDate, CalcDate) AS [RelativeYears]
, RIGHT('0' + convert(VARCHAR(2), MONTH(CalcDate)), 2) + '/' + Right('0' + convert(VARCHAR(2), DAY(CalcDate)), 2) + '/' + convert(VARCHAR(4), YEAR(CalcDate))
, CASE DATEPART(DW, CalcDate)
WHEN 1
THEN 0
WHEN 2
THEN 1
WHEN 3
THEN 1
WHEN 4
THEN 1
WHEN 5
THEN 1
WHEN 6
THEN 1
WHEN 7
THEN 0
END AS [WeekDayFlag]
, 0 AS HolidayFlag
, CASE DATEPART(DW, CalcDate)
WHEN 1
THEN 0
WHEN 2
THEN 1
WHEN 3
THEN 1
WHEN 4
THEN 1
WHEN 5
THEN 1
WHEN 6
THEN 1
WHEN 7
THEN 1
END AS OpenFlag
, CASE DATEPART(dd, CalcDate)
WHEN 1
THEN 1
ELSE 0
END AS [FirstDayOfCalendarMonthFlag]
, CASE
WHEN DateAdd(day, - 1, DateAdd(month, DateDiff(month, 0, CalcDate) + 1, 0)) = CalcDate
THEN 1
ELSE 0
END AS [LastDayOfCalendarMonthFlag]
FROM (
SELECT [Surrogate Key] AS IDNo
, @StartDate + [Surrogate Key] AS CalcDate
, datepart(DW, @StartDate + [Surrogate Key]) AS DoW
, year(@StartDate + [Surrogate Key]) AS YearNo
, month(@StartDate + [Surrogate Key]) AS MonthNo
FROM (
SELECT TOP (
SELECT cast(365.25 * 300 AS BIGINT)
) ROW_NUMBER() OVER (
ORDER BY getdate()
) AS [Surrogate Key]
FROM sys.syscolumns s1
, sys.syscolumns s2
) j
) k
ORDER BY [DateSK];
UPDATE dbo.DimDate
SET HolidayText = 'New Year''s Day'
, HolidayFlag = 1
, OpenFlag = 0
WHERE [CalendarMonthNumber] = 1
AND [DAY] = 1
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
SELECT CASE
WHEN DayOfWeek = 'Sunday'
THEN DATESK + 1
END
FROM DimDate
WHERE CalendarMonthNumber = 1
AND [DAY] = 1
)
UPDATE DimDate
SET HolidayText = 'Martin Luther King Jr. Day'
, HolidayFlag = 1
, OpenFlag = 0
WHERE [CalendarMonthNumber] = 1
AND [Dayofweek] = 'Monday'
AND CalendarYearNumber >= 1983
AND [DayOfWeekInMonth] = 3
GO
UPDATE DimDate
SET HolidayText = 'President''s Day'
, HolidayFlag = 1
, OpenFlag = 0
WHERE [CalendarMonthNumber] = 2
AND [Dayofweek] = 'Monday'
AND [DayOfWeekInMonth] = 3
GO
UPDATE dbo.DimDate
SET HolidayText = 'Memorial Day'
, HolidayFlag = 1
, OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
SELECT MAX([DateSK])
FROM dbo.DimDate
WHERE [CalendarMonthName] = 'May'
AND [DayOfWeek] = 'Monday'
GROUP BY CalendarYearNumber
, [CalendarMonthNumber]
)
UPDATE dbo.DimDate
SET HolidayText = 'Independance Day'
, HolidayFlag = 1
, OpenFlag = 0
WHERE [CalendarMonthNumber] = 7
AND [DAY] = 4
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
SELECT CASE
WHEN DayOfWeek = 'Sunday'
THEN DATESK + 1
END
FROM DimDate
WHERE CalendarMonthNumber = 7
AND [DAY] = 4
)
UPDATE dbo.DimDate
SET HolidayText = 'Labor Day'
, HolidayFlag = 1
, OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
SELECT MIN([DateSK])
FROM dbo.DimDate
WHERE [CalendarMonthName] = 'September'
AND [DayOfWeek] = 'Monday'
GROUP BY CalendarYearNumber
, [CalendarMonthNumber]
)
UPDATE dbo.DimDate
SET HolidayText = 'Columbus Day'
, HolidayFlag = 1
, OpenFlag = 0
FROM DimDate
WHERE DateSK IN (
SELECT MIN(DateSK)
FROM dbo.DimDate
WHERE [CalendarMonthName] = 'October'
AND [DayOfWeek] = 'Monday'
AND [DayOfWeekInMonth] = 2
GROUP BY CalendarYearNumber
, [CalendarMonthNumber]
)
UPDATE DimDate
SET HolidayText = 'Veteran''s Day'
, HolidayFlag = 1
, OpenFlag = 0
WHERE DateSK IN (
SELECT CASE
WHEN DayOfWeek = 'Saturday'
THEN DateSK - 1
WHEN DayOfWeek = 'Sunday'
THEN DateSK + 1
ELSE DateSK
END AS VeteransDateSK
FROM DimDate
WHERE [CalendarMonthNumber] = 11
AND [DAY] = 11
)
GO
UPDATE DimDate
SET HolidayText = 'Thanksgiving Day'
, HolidayFlag = 1
, OpenFlag = 0
WHERE [CalendarMonthNumber] = 11
AND [DAYOFWEEK] = 'Thursday'
AND [DayOfWeekInMonth] = 4
GO
UPDATE dbo.DimDate
SET HolidayText = 'Christmas Day'
, HolidayFlag = 1
, OpenFlag = 0
WHERE [CalendarMonthNumber] = 12
AND [DAY] = 25
UPDATE dbo.DimDate
SET OpenFlag = 0
WHERE DateSK IN (
SELECT CASE
WHEN DayOfWeek = 'Sunday'
THEN DATESK + 1
WHEN Dayofweek = 'Saturday'
THEN DateSK - 1
END
FROM DimDate
WHERE CalendarMonthNumber = 12
AND DAY = 25
)
UPDATE dbo.DimDate
SET HolidayText = 'Valentine''s Day'
WHERE CalendarMonthNumber = 2
AND [DAY] = 14
UPDATE dbo.DimDate
SET HolidayText = 'Saint Patrick''s Day'
WHERE [CalendarMonthNumber] = 3
AND [DAY] = 17
GO
UPDATE DimDate
SET HolidayText = 'Mother''s Day'
WHERE [CalendarMonthNumber] = 5
AND [Dayofweek] = 'Sunday'
AND [DayOfWeekInMonth] = 2
GO
UPDATE DimDate
SET HolidayText = 'Father''s Day'
WHERE [CalendarMonthNumber] = 6
AND [Dayofweek] = 'Sunday'
AND [DayOfWeekInMonth] = 3
GO
UPDATE dbo.DimDate
SET HolidayText = 'Halloween'
WHERE [CalendarMonthNumber] = 10
AND [DAY] = 31
UPDATE tues
SET HolidayFlag = 1
, HolidayText = 'Election Day'
FROM dimdate AS tues
INNER JOIN dimdate AS mon
ON tues.fulldate = mon.fulldate + 1
WHERE mon.CalendarMonthNumber = 11
AND mon.[DayOfWeek] = 'Monday'
AND mon.DayOfWeekInMonth = 1
GO
PRINT CONVERT(VARCHAR, GETDATE(), 113)
CREATE UNIQUE NONCLUSTERED INDEX [IDX_DimDate_Date] ON [dbo].[DimDate] ([FullDate] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_Day] ON [dbo].[DimDate] ([Day] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_DayOfWeek] ON [dbo].[DimDate] ([DayOfWeek] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_DOWInMonth] ON [dbo].[DimDate] ([DayOfWeekInMonth] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_DayOfYear] ON [dbo].[DimDate] ([DayOfYearNumber] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_WeekOfYear] ON [dbo].[DimDate] ([WeekOfYearNumber] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_WeekOfMonth] ON [dbo].[DimDate] ([WeekOfMonthNumber] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_Month] ON [dbo].[DimDate] ([CalendarMonthNumber] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_MonthName] ON [dbo].[DimDate] ([CalendarMonthName] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_Quarter] ON [dbo].[DimDate] ([CalendarQuarterNumber] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_QuarterName] ON [dbo].[DimDate] ([CalendarQuarterName] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_DimDate_Year] ON [dbo].[DimDate] ([CalendarYearNumber] 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
, FILLFACTOR = 90
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IDX_dim_Time_HolidayText] ON [dbo].[DimDate] ([HolidayText] 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
, FILLFACTOR = 90
) ON [PRIMARY]
PRINT convert(VARCHAR, getdate(), 113)
