demonstrating use of permanent calendar table, Sql Server

Language: Layout:

CREATE FUNCTION dbo.Computus
-- Computus (Latin for computation) is the calculation of the date of
-- Easter in the Christian calendar
-- http://en.wikipedia.org/wiki/Computus
-- I'm using the Meeus/Jones/Butcher Gregorian algorithm
(@Y int -- The year we are calculating easter sunday for
)
RETURNS datetime
AS
BEGIN
    DECLARE @a int
          , @b int
          , @c int
          , @d int
          , @e int
          , @f int
          , @g int
          , @h int
          , @i int
          , @k int
          , @L int
          , @m int

SET @a = @Y % 19
    SET @b = @Y / 100
    SET @c = @Y % 100
    SET @d = @b / 4
    SET @e = @b % 4
    SET @f = (@b + 8) / 25
    SET @g = (@b - @f + 1) / 3
    SET @h = (19 * @a + @b - @d - @g + 15) % 30
    SET @i = @c / 4
    SET @k = @c % 4
    SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
    SET @m = (@a + 11 * @h + 22 * @L) / 451
    RETURN (DATEADD(MONTH, ((@h + @L - 7 * @m + 114) / 31) - 1, CAST(CAST(@Y AS varchar) AS datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END
GO

CREATE TABLE [dbo].[Calendar] (
    -- This is the calendar table
    [Date] datetime NOT NULL
  , [Year] int NOT NULL
  , [Quarter] int NOT NULL
  , [Month] int NOT NULL
  , [Week] int NOT NULL
  , [Day] int NOT NULL
  , [DayOfYear] int NOT NULL
  , [Weekday] int NOT NULL
  , [Fiscal_Year] int NOT NULL
  , [Fiscal_Quarter] int NOT NULL
  , [Fiscal_Month] int NOT NULL
  , [KindOfDay] varchar(10) NOT NULL
  , [Description] varchar(50) NULL
  , PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE [dbo].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))
-- http://www.celko.com/

ADD CONSTRAINT [Calendar_ck] CHECK (([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week] BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO

SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- http://en.wikipedia.org/wiki/ISO_8601
-- thus first day of a week is monday
WITH
    dates (Date)
    -- A recursive CTE that produce all dates between 1999 and 2020-12-31
    AS (
            SELECT
                CAST('2015' AS datetime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
            UNION ALL                            -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
            SELECT
                (Date + 1) AS date
            FROM Dates
            WHERE Date < CAST('2025' AS datetime) - 1
        ),

datesandthursdayinweek (Date, Thursday)
    -- The weeks can be found by counting the thursdays in a year so we find
    -- the thursday in the week for a particular date
    AS (
            SELECT
                Date
              , CASE DATEPART(WEEKDAY, Date)
                    WHEN 1
                    THEN Date + 3
                    WHEN 2
                    THEN Date + 2
                    WHEN 3
                    THEN Date + 1
                    WHEN 4
                    THEN Date
                    WHEN 5
                    THEN Date - 1
                    WHEN 6
                    THEN Date - 2
                    WHEN 7
                    THEN Date - 3
                END AS thursday
            FROM dates
        ),

weeks (Week, Thursday)
    -- Now we produce the weeknumers for the thursdays
    -- ROW_NUMBER is new to SQL Server 2005
    AS (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY YEAR(Date) ORDER BY Date) week
              , Thursday
            FROM datesandthursdayinweek
            WHERE DATEPART(WEEKDAY, Date) = 4
        )
INSERT INTO dbo.Calendar
    SELECT
        d.Date
      , YEAR(d.Date)                AS year
      , DATEPART(QUARTER, d.Date)   AS quarter
      , MONTH(d.Date)               AS month
      , w.Week
      , DAY(d.Date)                 AS day
      , DATEPART(DAYOFYEAR, d.Date) AS dayofyear
      , DATEPART(WEEKDAY, d.Date)   AS weekday
      ,

-- Fiscal year may be different to the actual year in Norway the are the same
        -- http://en.wikipedia.org/wiki/Fiscal_year
        YEAR(d.Date)                AS fiscal_year
      , DATEPART(QUARTER, d.Date)   AS fiscal_quarter
      , MONTH(d.Date)               AS fiscal_month
      , CASE
            -- Holidays in Norway
            -- For other countries and states: Wikipedia - List of holidays by country
            -- http://en.wikipedia.org/wiki/List_of_holidays_by_country
            WHEN (DATEPART(DAYOFYEAR, d.Date) = 1)          -- New Year's Day
                OR (d.Date = dbo.Computus(YEAR(Date)) - 7)  -- Palm Sunday
                OR (d.Date = dbo.Computus(YEAR(Date)) - 3)  -- Maundy Thursday
                OR (d.Date = dbo.Computus(YEAR(Date)) - 2)  -- Good Friday
                OR (d.Date = dbo.Computus(YEAR(Date)))    -- Easter Sunday
                OR (d.Date = dbo.Computus(YEAR(Date)) + 39) -- Ascension Day
                OR (d.Date = dbo.Computus(YEAR(Date)) + 49) -- Pentecost
                OR (d.Date = dbo.Computus(YEAR(Date)) + 50) -- Whitmonday
                OR (MONTH(d.Date) = 5 AND
                DAY(d.Date) = 1)      -- Labour day
                OR (MONTH(d.Date) = 5 AND
                DAY(d.Date) = 17)     -- Constitution day
                OR (MONTH(d.Date) = 12 AND
                DAY(d.Date) = 25)    -- Cristmas day
                OR (MONTH(d.Date) = 12 AND
                DAY(d.Date) = 26)    -- Boxing day
            THEN 'HOLIDAY'
            WHEN DATEPART(WEEKDAY, d.Date) = 6
            THEN 'SATURDAY'
            WHEN DATEPART(WEEKDAY, d.Date) = 7
            THEN 'SUNDAY'
            ELSE 'BANKDAY'
        END                         kindofday
      , CASE
            -- Description of holidays in Norway
            WHEN (DATEPART(DAYOFYEAR, d.Date) = 1)
            THEN 'New Year''s Day'
            WHEN (d.Date = dbo.Computus(YEAR(Date)) - 7)
            THEN 'Palm Sunday'
            WHEN (d.Date = dbo.Computus(YEAR(Date)) - 3)
            THEN 'Maundy Thursday'
            WHEN (d.Date = dbo.Computus(YEAR(Date)) - 2)
            THEN 'Good Friday'
            WHEN (d.Date = dbo.Computus(YEAR(Date)))
            THEN 'Easter Sunday'
            WHEN (d.Date = dbo.Computus(YEAR(Date)) + 39)
            THEN 'Ascension Day'
            WHEN (d.Date = dbo.Computus(YEAR(Date)) + 49)
            THEN 'Pentecost'
            WHEN (d.Date = dbo.Computus(YEAR(Date)) + 50)
            THEN 'Whitmonday'
            WHEN (MONTH(d.Date) = 5 AND
                DAY(d.Date) = 1)
            THEN 'Labour day'
            WHEN (MONTH(d.Date) = 5 AND
                DAY(d.Date) = 17)
            THEN 'Constitution day'
            WHEN (MONTH(d.Date) = 12 AND
                DAY(d.Date) = 25)
            THEN 'Cristmas day'
            WHEN (MONTH(d.Date) = 12 AND
                DAY(d.Date) = 26)
            THEN 'Boxing day'
        END                         description

FROM datesandthursdayinweek d
    -- This join is for getting the week into the result set
    INNER JOIN weeks w ON d.Thursday = w.Thursday

OPTION (MAXRECURSION 0)
GO

CREATE FUNCTION dbo.Numbers (@AFrom int,
@ATo int,
@AIncrement int)
RETURNS @RetNumbers TABLE (
    [Number] int PRIMARY KEY NOT NULL
)
AS
BEGIN
    WITH
        numbers (n) AS (
                SELECT
                    @AFrom AS n
                UNION ALL
                SELECT
                    (n + @AIncrement) AS n
                FROM Numbers
                WHERE n < @ATo
            )
    INSERT @RetNumbers
        SELECT
            n
        FROM numbers
        OPTION (MAXRECURSION 0)
    RETURN;
END
GO

CREATE FUNCTION dbo.iNumbers (@AFrom int,
@ATo int,
@AIncrement int)
RETURNS TABLE
AS
    RETURN (
    WITH
        numbers (n) AS (
                SELECT
                    @AFrom AS n
                UNION ALL
                SELECT
                    (n + @AIncrement) AS n
                FROM Numbers
                WHERE n < @ATo
            )
    SELECT
        n AS number
    FROM numbers
    )
GO

CREATE TABLE WorkSchedules(
   Id   INTEGER  NOT NULL PRIMARY KEY 
  ,[From] DATE  NOT NULL
  ,[To]   DATE  NOT NULL
);
INSERT INTO WorkSchedules(Id,[From],[To]) VALUES (1,'2018-01-01','2018-01-05');
INSERT INTO WorkSchedules(Id,[From],[To]) VALUES (2,'2018-01-12','2018-01-12');

with range as (
    select min(ws.[From]) as dt_from, max(ws.[To]) dt_to
    from WorkSchedules as ws
    )
select c.*
from calendar as c
inner join range on c.date between range.dt_from and range.dt_to
where KindOfDay = 'BANKDAY'
order by c.date

/* ttps://stackoverflow.com/questions/52451413/get-all-dates-for-all-dateranges-in-table-using-sql-server/ */

View schema
Execution time: 0,98 sec, rows selected: 9, rows affected: 3653, absolute service time: 1,16 sec