SQL Server - Multiple Holidays Model, Sql Server

SQL Server - Multiple Holidays Model

--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
);

--------------------------------------------------------------------------------
-- Create Holiday Table
--------------------------------------------------------------------------------
CREATE TABLE holiday_t
(
  [HolidayID]            BIGINT       NOT NULL IDENTITY(1,1) PRIMARY KEY,
  [HolidayDate]          DATE         NOT NULL,
  [HolidayIsBankHoliday] INT          NOT NULL,
  [HolidayDescription]   VARCHAR(100) NULL,
  FOREIGN KEY ([HolidayDate]) REFERENCES date_t([TheDate])
);

CREATE
 INDEX DT_holiday_t
    ON holiday_t
      ([HolidayDate])
;

--------------------------------------------------------------------------------
-- 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])
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]
  FROM date_sequence
;

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

--------------------------------------------------------------------------------
-- View Holidays
--
-- CTE using windowing makes sure we only get one result for each date
--------------------------------------------------------------------------------
WITH bank_holidays AS (
SELECT DISTINCT
       h.[HolidayDate],
       FIRST_VALUE(h.[HolidayIsBankHoliday]) OVER (PARTITION BY h.[HolidayDate] ORDER BY [HolidayID] DESC) AS [HolidayIsBankHoliday],
       FIRST_VALUE(h.[HolidayDescription]  ) OVER (PARTITION BY h.[HolidayDate] ORDER BY [HolidayID] DESC) AS [HolidayDescription]
  FROM holiday_t h
 WHERE h.[HolidayIsBankHoliday] = 1
)
SELECT d.[TheDate],
       DateName(dw, d.[TheDate]) AS [DayName],
       d.[IsWeekend],
       d.[IsWeekday],
       h.[HolidayIsBankHoliday],
       h.[HolidayDescription]
  FROM date_t d
     LEFT JOIN bank_holidays h
       ON h.[HolidayDate] = d.[TheDate]
 WHERE h.[HolidayIsBankHoliday] = 1
 ORDER BY d.[TheDate]
;


run \| edit \| history \| help	0