SQL Server - UK Holiday Calendar Example, Sql Server

Language: Layout:

--Set the start of the week to Monday
SET DATEFIRST 1;

--------------------------------------------------------------------------------
-- Create Date Table
--------------------------------------------------------------------------------
CREATE TABLE date_t
(
  [TheDate]            DATE         NOT NULL PRIMARY KEY,
  [IsWeekend]          INT          NOT NULL,
  [IsWeekday]          INT          NOT NULL,
  [IsHoliday]          INT          NOT NULL DEFAULT 0,
  [HolidayDescription] VARCHAR(100) NULL
);

--------------------------------------------------------------------------------
-- Create Date list, and insert to table
--------------------------------------------------------------------------------
DECLARE @DateStart DATE = DateFromParts(2009, 01, 01); -- At time of writing, in the past
DECLARE @DateEnd   DATE = DateFromParts(2020, 12, 31); -- At time of writing, in the future
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       t4.a AS t4a,
       (t1.a + (10 * t2.a) + (100 * t3.a) + (1000 * t4.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
  CROSS JOIN number_tbl t4
), date_sequence AS (
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
)
INSERT
  INTO date_t
      ([TheDate], [IsWeekend], [IsWeekday], [IsHoliday])
SELECT [TheDate],
       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 1 ELSE 0 END AS [IsWeekend],
       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 0 ELSE 1 END AS [IsWeekday],
       0 AS [IsHoliday]
  FROM date_sequence
;

--------------------------------------------------------------------------------
-- Create Holidays
--------------------------------------------------------------------------------
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2009-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2009-04-10', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2009-04-13', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2009-05-04', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2009-05-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2009-08-31', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2009-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2009-12-28', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2010-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2010-04-02', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2010-04-05', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2010-05-03', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2010-05-31', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2010-08-30', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2010-12-27', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2010-12-28', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2011-01-03', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2011-04-22', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2011-04-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2011-05-02', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2011-05-30', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2011-08-29', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2011-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2011-12-27', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2012-01-02', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2012-04-06', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2012-04-09', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2012-05-07', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2012-05-28', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2012-08-27', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2012-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2012-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2013-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2013-03-29', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2013-04-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2013-05-06', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2013-05-27', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2013-08-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2013-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2013-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2014-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2014-04-18', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2014-04-21', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2014-05-05', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2014-05-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2014-08-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2014-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2014-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2015-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2015-04-03', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2015-04-06', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2015-05-04', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2015-05-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2015-08-31', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2015-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2015-12-28', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2016-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2016-03-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2016-03-28', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2016-05-02', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2016-05-30', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2016-08-29', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2016-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2016-12-27', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2017-01-02', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2017-04-14', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2017-04-17', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2017-05-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2017-05-29', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2017-08-28', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2017-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2017-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2018-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2018-03-30', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2018-04-02', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2018-05-07', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2018-05-28', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2018-08-27', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2018-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2018-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2019-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2019-04-19', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2019-04-22', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2019-05-06', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2019-05-27', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2019-08-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2019-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2019-12-26', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'New Year''s Day' WHERE [TheDate] = CONVERT(DATE, '2020-01-01', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Good Friday' WHERE [TheDate] = CONVERT(DATE, '2020-04-10', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Easter Monday' WHERE [TheDate] = CONVERT(DATE, '2020-04-13', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'May Day (Early May Bank Holiday)' WHERE [TheDate] = CONVERT(DATE, '2020-05-04', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Spring Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2020-05-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Summer Bank Holiday' WHERE [TheDate] = CONVERT(DATE, '2020-08-31', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Christmas' WHERE [TheDate] = CONVERT(DATE, '2020-12-25', 120);
UPDATE date_t SET [IsHoliday] = 1, [HolidayDescription] = 'Boxing Day' WHERE [TheDate] = CONVERT(DATE, '2020-12-28', 120);

--------------------------------------------------------------------------------
-- View Holidays
--------------------------------------------------------------------------------
SELECT [TheDate],
       DateName(dw, [TheDate]) AS [DayName],
       [IsWeekend],
       [IsWeekday],
       [IsHoliday],
       [HolidayDescription]
  FROM date_t
 WHERE [IsHoliday] = 1
 ORDER BY [TheDate]
;

View schema
Execution time: 0,12 sec, rows selected: 98, rows affected: 4479, absolute service time: 0,31 sec