Must be that time of year again :). Adapted from Aaron’s beautiful US calendar script …
-- CalendarUK.sql
use [Dev];
-- initialize period
DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;
-- prevent set or regional settings from interfering with
-- interpretation of dates / literals
SET DATEFIRST 7; -- sunday is the first day of week
SET DATEFORMAT mdy; -- thats month/day/year
SET LANGUAGE US_ENGLISH;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
-- 1. this is just a holding table for intermediate calculations:
IF OBJECT_ID('tempdb..#cal') IS NOT NULL DROP TABLE #cal
CREATE TABLE #cal
(
[date] DATE PRIMARY KEY,
[day] AS DATEPART(DAY, [date]),
[month] AS DATEPART(MONTH, [date]),
FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
[MonthName] AS DATENAME(MONTH, [date]),
[week] AS DATEPART(WEEK, [date]),
[ISOweek] AS DATEPART(ISO_WEEK, [date]),
[DayOfWeek] AS DATEPART(WEEKDAY, [date]),
[quarter] AS DATEPART(QUARTER, [date]),
[year] AS DATEPART(YEAR, [date]),
FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),
Style112 AS CONVERT(CHAR(8), [date], 112),
Style101 AS CONVERT(CHAR(10), [date], 101)
);
-- use the catalog views to generate as many rows as we need
INSERT #cal([date])
SELECT d
FROM
(
SELECT d = 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;
-- 2. create the real table
IF OBJECT_ID('dbo.CalendarUK') IS NOT NULL DROP TABLE dbo.CalendarUK
CREATE TABLE [dbo].[CalendarUK]
(
DateKey INT NOT NULL PRIMARY KEY,
[Date] DATE NOT NULL,
[Day] TINYINT NOT NULL,
DaySuffix CHAR(2) NOT NULL,
[Weekday] TINYINT NOT NULL,
WeekDayName VARCHAR(10) NOT NULL,
IsWeekend BIT NOT NULL,
IsHoliday BIT NOT NULL,
HolidayText VARCHAR(64) SPARSE,
DOWInMonth TINYINT NOT NULL,
[DayOfYear] SMALLINT NOT NULL,
WeekOfMonth TINYINT NOT NULL,
WeekOfYear TINYINT NOT NULL,
ISOWeekOfYear TINYINT NOT NULL,
[Month] TINYINT NOT NULL,
[MonthName] VARCHAR(10) NOT NULL,
[Quarter] TINYINT NOT NULL,
QuarterName VARCHAR(6) NOT NULL,
[Year] INT NOT NULL,
MMYYYY CHAR(6) NOT NULL,
MonthYear CHAR(7) NOT NULL,
FirstDayOfMonth DATE NOT NULL,
LastDayOfMonth DATE NOT NULL,
FirstDayOfQuarter DATE NOT NULL,
LastDayOfQuarter DATE NOT NULL,
FirstDayOfYear DATE NOT NULL,
LastDayOfYear DATE NOT NULL,
FirstDayOfNextMonth DATE NOT NULL,
FirstDayOfNextYear DATE NOT NULL
);
GO
-- 3 populate the real table from the temp table
INSERT dbo.CalendarUK WITH (TABLOCKX)
SELECT
DateKey = CONVERT(INT, Style112),
[Date] = [date],
[Day] = CONVERT(TINYINT, [day]),
DaySuffix = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE
CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd' ELSE 'th' END END),
[Weekday] = CONVERT(TINYINT, [DayOfWeek]),
[WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
[IsWeekend] = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
[IsHoliday] = CONVERT(BIT, 0),
HolidayText = CONVERT(VARCHAR(64), NULL),
[DOWInMonth] = CONVERT(TINYINT, ROW_NUMBER() OVER
(PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
[DayOfYear] = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
WeekOfMonth = CONVERT(TINYINT, DENSE_RANK() OVER
(PARTITION BY [year], [month] ORDER BY [week])),
WeekOfYear = CONVERT(TINYINT, [week]),
ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
[Month] = CONVERT(TINYINT, [month]),
[MonthName] = CONVERT(VARCHAR(10), [MonthName]),
[Quarter] = CONVERT(TINYINT, [quarter]),
QuarterName = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First'
WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END),
[Year] = [year],
MMYYYY = CONVERT(CHAR(6), LEFT(Style101, 2) + LEFT(Style112, 4)),
MonthYear = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
FirstDayOfMonth = FirstOfMonth,
LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),
FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),
LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),
FirstDayOfYear = FirstOfYear,
LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),
FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)
FROM #cal
OPTION (MAXDOP 1);
-- 4 add holidays
;WITH x AS
(
SELECT DateKey, [Date], IsHoliday, HolidayText, FirstDayOfYear,
DOWInMonth, [MonthName], [WeekDayName], [Day],
LastDOWInMonth = ROW_NUMBER() OVER
(
PARTITION BY FirstDayOfMonth, [Weekday]
ORDER BY [Date] DESC
)
FROM dbo.CalendarUK
)
UPDATE x SET IsHoliday = 1, HolidayText = CASE
WHEN ([Date] = FirstDayOfYear) THEN 'New Years Day'
WHEN ([DOWInMonth] = 3 AND [MonthName] = 'April' AND [WeekDayName] = 'Friday') THEN 'Good Friday' -- (3rd Monday in January)
WHEN ([DOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday') THEN 'May Day' -- (first Monday in May)
WHEN ([LastDOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday') THEN 'May Bank Holiday' -- (last Monday in May)
WHEN ([LastDOWInMonth] = 1 AND [MonthName] = 'August' AND [WeekDayName] = 'Monday') THEN 'August Bank Hoiliday' -- (last Monday in August)
WHEN ([MonthName] = 'December' AND [Day] = 25) THEN 'Christmas Day'
WHEN ([MonthName] = 'December' AND [Day] = 26) THEN 'Boxing Day'
END
WHERE -- IsHoliday
([Date] = FirstDayOfYear)
OR ([LastDOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday')
OR ([DOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday')
OR ([LastDOWInMonth] = 1 AND [MonthName] = 'August' AND [WeekDayName] = 'Monday')
OR ([MonthName] = 'December' AND [Day] = 25)
OR ([MonthName] = 'December' AND [Day] = 26);
-- 5. create a function to calculate easter etc
IF OBJECT_ID('dbo.GetEasterHolidays') IS NOT NULL DROP FUNCTION dbo.GetEasterHolidays
GO
CREATE FUNCTION dbo.GetEasterHolidays(@year INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
WITH x AS
(
SELECT [Date] = CONVERT(DATE, RTRIM(@year) + '0' + RTRIM([Month])
+ RIGHT('0' + RTRIM([Day]),2))
FROM (SELECT [Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4))
FROM (SELECT [Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday
FROM (SELECT DaysToSunday = paschal - ((@year + @year / 4 + paschal - 13) % 7)
FROM (SELECT paschal = epact - (epact / 28)
FROM (SELECT epact = (24 + 19 * (@year % 19)) % 30)
AS epact) AS paschal) AS dts) AS m) AS d
)
SELECT DATEADD(DAY,-2,[Date]) [Date], 'Good Friday' HolidayName FROM x
UNION ALL SELECT DATEADD(DAY, 1,[Date]), 'Easter Monday' FROM x
);
GO
-- 6. use the function to insert easter etc
;WITH x AS
(
SELECT d.[Date], d.IsHoliday, d.HolidayText, h.HolidayName
FROM dbo.CalendarUK AS d
CROSS APPLY dbo.GetEasterHolidays(d.[Year]) AS h
WHERE d.[Date] = h.[Date]
)
UPDATE x SET IsHoliday = 1, HolidayText = HolidayName;
-- 7. show results
SELECT *
FROM dbo.CalendarUK
WHERE [year] = '2019'
--WHERE [year] in ('2019', '2020')
AND (IsHoliday = 1
OR HolidayText IS NOT NULL)
--and DateKey = '20181231'
Database Consultant 