Building calendar table using MTVF

/*
This script is a small extension to this excellent article
http://www.sqlservercentral.com/articles/calendar/145206/

I'm trying to do solution explain in this article, more practical by utilizing the MTVF's.

In addition, in the script is corrected minor bugs related to marking the day of the week
or determining weekdays.

Holidays are determined for Croatia. But fixed holidays can easily be replaced with appropriate in your country.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Comments about MTVF
https://blogs.msdn.microsoft.com/psssql/2010/10/28/query-performance-and-multi-statement-table-valued-functions/
If you don’t plan to join a multi-statement TVF with other tables, you are OK because the low cardinality estimate
doesn’t matter.
If you know that your multi-statement TVF will always return small number of rows, you are OK as well.
If you anticipate large number of rows will result from executing the multi-statement TVF and you will need to join
this TVF with other tables, consider putting the results from the TVF to a temp table and then join
with the temp table.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

There are lots of places where it can be applied. 
1. Almost every software vendor in its standard database model has a table of holidays.
Customers love instead of entering the holiday, clicking on the button labled "Get It From Template!".
Handler of this button is good place to implement MTVF.
2. The same applies to the calendar table. In many companies the calendar is based per employee.
3. In complex queries this MTVF's should be used for filling temp tables. 


And finally, maybe is not bed idea to build the calendar table for countries in sys.syslanguages. 
DECLARE @countries AS NVARCHAR(MAX)= '';
SELECT
@countries = @countries + RTRIM(name) + ','
FROM SYS.syslanguages
ORDER BY name;
SELECT @countries;
In that case the calendar table should have a parametar called cantry code.

ALL GLORY, STILL BELONGS TO THE AUTHOR OF THE ARTICLE MENTIONED ABOVE!
Cheers!
*/
--Create schema Calendar if not exists
IF NOT EXISTS (SELECT
schema_name
FROM information_schema.schemata
WHERE schema_name = 'Calendar')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA [Calendar]';
END;
GO

--Create function [Calendar].[DetermineEaster]
IF OBJECT_ID(N'[Calendar].[DetermineEaster]', N'FN') IS NOT NULL
    BEGIN
DROP FUNCTION [Calendar].[DetermineEaster];
END;
GO


CREATE FUNCTION [Calendar].[DetermineEaster]
(@inputYear               INT,
 @isEasterOrCorpusChristi BIT
)
RETURNS DATE
     BEGIN
         --RETURNS datetime on 2005v.

/*********************************************************************************************
2016 Darko Martinović
Examples :
SELECT [Calendar].[DetermineEaster](2016,0) ->Corpus Christ
SELECT [Calendar].[DetermineEaster](2016,1) ->Easter
SELECT dateAdd(day,-46,[Calendar].[DetermineEaster](2016)) --Powdery mildew
SELECT dateAdd(day,-7,[Calendar].[DetermineEaster](2016)) --Palm Sunday

*********************************************************************************************/
         DECLARE @y INTEGER, @dy INTEGER, @TheEaster VARCHAR(10), @TheMonthOfEaster INTEGER, @DayOfEaster INTEGER;
SET @y = @inputYear;
SET @dy = ((19 * (@y % 19) + (@y / 100) - ((@y / 100) / 4) - (((@y / 100) - (((@y / 100) + 8) / 25) + 1) / 3) + 15) % 30) + ((32 + 2 * ((@y / 100) % 4) + 2 * ((@y % 100) / 4) - ((19 * (@y % 19) + (@y / 100) - ((@y / 100) / 4) - (((@y / 100) - (((@y / 100) + 8) / 25) + 1) / 3) + 15) % 30) - ((@y % 100) % 4)) % 7) - 7 * (((@y % 19) + 11 * ((19 * (@y % 19) + (@y / 100) - ((@y / 100) / 4) - (((@y / 100) - (((@y / 100) + 8) / 25) + 1) / 3) + 15) % 30) + 22 * ((32 + 2 * ((@y / 100) % 4) + 2 * ((@y % 100) / 4) - ((19 * (@y % 19) + (@y / 100) - ((@y / 100) / 4) - (((@y / 100) - (((@y / 100) + 8) / 25) + 1) / 3) + 15) % 30) - ((@y % 100) % 4)) % 7)) / 451) + 114;
SET @TheMonthOfEaster = @dy / 31;
SET @DayOfEaster = (@dy % 31) + 1;
SET @TheEaster = CAST(@y AS VARCHAR(4)) + RIGHT('00' + CAST(@TheMonthOfEaster AS VARCHAR(2)), 2) + RIGHT('00' + CAST(@DayOfEaster AS VARCHAR(2)), 2);
         IF @isEasterOrCorpusChristi = 0
             BEGIN

                 --Return CorpusChristi
                 RETURN DATEADD(DAY, 60, CAST(@TheEaster AS DATE));
             END;
RETURN CAST(@TheEaster AS DATE);
END;
GO



/*********************************************************************************************
Easters( and CorpusChrist ) table
2016 Darko Martinović
Examples :
SELECT
*
FROM CALENDAR.EASTERS(2017, 2018);

TheYear     TheEaster  TheCorpusChrist
----------- ---------- ---------------
2017        2017-04-16 2017-06-15
2018        2018-04-01 2018-05-31

*********************************************************************************************/
IF OBJECT_ID(N'[Calendar].[Easters]', N'TF') IS NOT NULL
    BEGIN
DROP FUNCTION [Calendar].[Easters];
END;
GO


CREATE FUNCTION [Calendar].[Easters]
(@LowerYear INT,
 @UpperYear INT
)
RETURNS @Easter TABLE
(TheYear         INT,
 TheEaster       DATE NOT NULL,
 TheCorpusChrist DATE NOT NULL,
 PRIMARY KEY(TheYear)
)
     BEGIN
         DECLARE @CurrentDate DATE;
         DECLARE @endYear VARCHAR(4);
         DECLARE @buffer AS INT;
         IF @lowerYear IS NULL OR @upperYear IS NULL
       RETURN;

IF @lowerYear > @upperYear
BEGIN
SET @buffer = @lowerYear;
SET @lowerYear = @upperYear;
SET @upperYear = @buffer;
  END
SET @CurrentDate = CAST(CAST(@LowerYear AS VARCHAR(4)) + '0101' AS DATE);
SET @endYear = CAST(@upperYear + 1 AS VARCHAR(4));
         WHILE DATEDIFF(YEAR, @CurrentDate, CAST(@endYear+'0101' AS DATE)) > 0
             BEGIN
INSERT INTO @Easter (TheYear,
TheEaster,
TheCorpusChrist)
SELECT
DATEPART(YEAR, @CurrentDate)
,[Calendar].[DetermineEaster](YEAR(@CurrentDate), 1)
,[Calendar].[DetermineEaster](YEAR(@CurrentDate), 0);
SET @CurrentDate = DATEADD(YEAR, 1, @CurrentDate);
             END;
RETURN;
END;
GO


/*********************************************************************************************
Numbers table
2016 Darko Martinović
Examples :
SELECT
*
FROM CALENDAR.Numbers(2);

N
-----------
1
2
*********************************************************************************************/
IF OBJECT_ID(N'[Calendar].[Numbers]', N'TF') IS NOT NULL
    BEGIN
DROP FUNCTION [Calendar].[Numbers];
END;
GO


CREATE FUNCTION [Calendar].[Numbers]
(@limit INT
)
RETURNS @numbers TABLE
(N INT PRIMARY KEY
       NOT NULL
)
     BEGIN
WITH lv0
AS
(SELECT
0 AS g
UNION ALL
SELECT
0),
lv1
AS
(SELECT
0 AS g
FROM lv0 AS a
CROSS JOIN lv0 AS b) -- 4
,
lv2
AS
(SELECT
0 AS g
FROM lv1 AS a
CROSS JOIN lv1 AS b) -- 16
,
lv3
AS
(SELECT
0 AS g
FROM lv2 AS a
CROSS JOIN lv2 AS b) -- 256
,
lv4
AS
(SELECT
0 AS g
FROM lv3 AS a
CROSS JOIN lv3 AS b) -- 65,536
,
lv5
AS
(SELECT
0 AS g
FROM lv4 AS a
CROSS JOIN lv4 AS b) -- 4,294,967,296
,
Helper (n)
AS
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT
NULL)
)
FROM lv5)
INSERT INTO @numbers
SELECT TOP (@limit)
n
FROM Helper;
RETURN;
END;
GO

/*********************************************************************************************
Holidays table ( Holidays in Croatia )
2016 Darko Martinović
Examples :
SELECT
*
FROM CALENDAR.HOLIDAYS('20170101', '20171231');
TheDate    HolidayName
---------- ----------------------------------------------------------------------------------------------------
2017-01-01 New Year
2017-01-06 Epiphany
2017-04-16 Easter
2017-04-17 Easter Monday
2017-05-01 Labor Day
2017-06-15 Corpus Christi
2017-06-22 Anti-Fascist Struggle Day
2017-06-25 Statehood Day
2017-08-05 Victory and Homeland Thanksgiving Day and the Day of Croatian Defenders
2017-08-15 Assumption of Mary
2017-10-08 Independence Day
2017-11-01 All Saints'Day
2017-12-25 Christmas
2017-12-26 St. Stephen's Day
*********************************************************************************************/
IF OBJECT_ID(N'[Calendar].[Holidays]', N'TF') IS NOT NULL
    BEGIN
DROP FUNCTION [Calendar].[Holidays];
END;
GO

CREATE FUNCTION [Calendar].[Holidays]
(@fromDate DATE,
 @toDate   DATE
)
RETURNS @holidays TABLE
(TheDate     DATE NOT NULL,
 HolidayName NVARCHAR(100) NOT NULL,
 PRIMARY KEY(TheDate, HolidayName)
)
     BEGIN
         DECLARE @buffer AS DATE;
        -- IF ISDATE(@fromDate) = 0 OR ISDATE(@toDate) = 0
       --RETURN;
         IF @fromDate > @toDate
             BEGIN
SET @buffer = @fromDate;
SET @fromDate = @toDate;
SET @toDate = @buffer;
             END;
DECLARE @difference AS INT;
SET @difference = YEAR(@toDate) - YEAR(@fromDate) + 1;
WITH FIXEDHolidays
AS
(SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '0101' TheDate
,'New Year' HolidayName --Nova godina
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '0106' TheDate
,'Epiphany' HolidayName --Sveta tri kralja'
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '0501' TheDate
,'Labor Day' HolidayName -- Praznik rada
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '0622' TheDate
,'Anti-Fascist Struggle Day' HolidayName --Dan antifašistčke borbe
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '0625' TheDate
,'Statehood Day' HolidayName --Dan državnosti
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '0805' TheDate
,'Victory and Homeland Thanksgiving Day and the Day of Croatian Defenders' HolidayName-- Dan domovinske zahvalnosti i hrvatskih branitelja
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '0815' TheDate
,'Assumption of Mary' HolidayName--Velika Gospa
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '1008' TheDate
,'Independence Day' HolidayName -- Dan nezavisnosti
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '1101' TheDate
,'All Saints''Day' HolidayName -- Svi sveti'
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '1225' TheDate
,'Christmas' HolidayName--Božić
UNION ALL
SELECT
CAST(YEAR(@fromDate) AS NVARCHAR(4)) + '1226' TheDate
,'St. Stephen''s Day' HolidayName --Sv.Stjepan
),
Expand
AS
(SELECT TOP (@difference * (SELECT
COUNT(*)
FROM fixedHolidays)
)
DATEADD(YEAR, N - 1, CONVERT(DATE, TheDate)) AS TheDate
,HolidayName
FROM Calendar.Numbers(@difference) AS t
CROSS JOIN FixedHolidays)
INSERT INTO @holidays (TheDate,
HolidayName)
SELECT
*
FROM (SELECT
*
FROM Expand
UNION ALL
SELECT
TheEaster
,'Easter' --'Uskrs'
FROM Calendar.Easters(YEAR(@fromDate), YEAR(@toDate))
UNION ALL
SELECT
DATEADD(DAY, 1, TheEaster)
,'Easter Monday' -- Uskrsni ponedjeljak'
FROM Calendar.Easters(YEAR(@fromDate), YEAR(@toDate))
UNION ALL
SELECT
TheCorpusChrist
,'Corpus Christi' --'Tijelovo'
FROM Calendar.Easters(YEAR(@fromDate), YEAR(@toDate))) AS HOLIDAYS
WHERE TheDate >= @fromDate
AND TheDate <= @toDate
ORDER BY TheDate;
RETURN;
END;
GO


/*********************************************************************************************
Calendar table
2016 Darko Martinović
Examples :
SELECT
CalendarDate
,DayOfTheWeek
,IsWeekEnd
,isHoliday
,isWorkedDay
,WorkedDayNo
FROM Calendar.Calendar('20170101', '20170131');

CalendarDate DayOfTheWeek IsWeekEnd isHoliday isWorkedDay WorkedDayNo
------------ ------------ --------- --------- ----------- -----------
2017-01-01   1            1         1         0           1
2017-01-02   2            0         0         1           1
2017-01-03   3            0         0         1           2
2017-01-04   4            0         0         1           3
2017-01-05   5            0         0         1           4
2017-01-06   6            0         1         0           2
2017-01-07   0            1         0         0           3
2017-01-08   1            1         0         0           4
2017-01-09   2            0         0         1           5
2017-01-10   3            0         0         1           6
2017-01-11   4            0         0         1           7
2017-01-12   5            0         0         1           8
2017-01-13   6            0         0         1           9
2017-01-14   0            1         0         0           5
2017-01-15   1            1         0         0           6
2017-01-16   2            0         0         1           10
2017-01-17   3            0         0         1           11
2017-01-18   4            0         0         1           12
2017-01-19   5            0         0         1           13
2017-01-20   6            0         0         1           14
2017-01-21   0            1         0         0           7
2017-01-22   1            1         0         0           8
2017-01-23   2            0         0         1           15
2017-01-24   3            0         0         1           16
2017-01-25   4            0         0         1           17
2017-01-26   5            0         0         1           18
2017-01-27   6            0         0         1           19
2017-01-28   0            1         0         0           9
2017-01-29   1            1         0         0           10
2017-01-30   2            0         0         1           20
2017-01-31   3            0         0         1           21


*********************************************************************************************/
IF OBJECT_ID(N'[Calendar].[Calendar]', N'TF') IS NOT NULL
    BEGIN
DROP FUNCTION [Calendar].[Calendar];
END;
GO


CREATE FUNCTION [Calendar].[Calendar]
(@fromDate DATE,
 @toDate   DATE
)
RETURNS @utCalendar TABLE
(DateId       INT PRIMARY KEY
                  NOT NULL,
 CalendarDate DATE,
 DayNo        INT,
 MonthNo      INT,
 YearNo       INT,
 DayOfTheWeek INT,
 isEndOfMonth BIT,
 isWeekEnd    BIT,
 isHoliday    BIT,
 isWorkedDay  BIT,
 WorkedDayNo  INT
)
     BEGIN
         DECLARE @saturday AS INT;
         DECLARE @sunday AS INT;
         DECLARE @numberofDays AS INT;
         DECLARE @buffer AS DATE;
        -- IF ISDATE(@fromDate) = 0 OR ISDATE(@toDate) = 0
       --RETURN;
         IF @fromDate > @toDate
             BEGIN
SET @buffer = @fromDate;
SET @fromDate = @toDate;
SET @toDate = @buffer;
             END;
SET @numberOfDays = DATEDIFF(DAY, @fromDate, DATEADD(DAY, 1, @toDate));

/*
Result WILL NOT depands on @@datefirst or language. In every batch you can issue
command like
SET LANGUAGE Italiano
or
SET DAYFIRST 2
So, we can not rely on internal tagging in sql server.
Similar,we can not rely on the names of the day, because they depend on language.
That's why is best to make a comparison with a known day and do calculation like this.
SELECT (DATEPART(WEEKDAY, '20161223') + @@DATEFIRST) % 7 AS [DayofWeek];
--Always return 6.
And we can determine sunday or saturday like this
SET @sunday = DATEPART(WEEKDAY, '20161002');
SET @saturday = DATEPART(WEEKDAY, '20161001');
We know that 01.10.2016 was saturday!
*/
SET @sunday = DATEPART(WEEKDAY, '20161002');
SET @saturday = DATEPART(WEEKDAY, '20161001');
WITH Dates (N, CalendarDate)
AS
(SELECT TOP (@numberOfDays)
N
,DATEADD(DAY, N - 1, CONVERT(DATE, @fromDate))
FROM Calendar.Numbers(@numberofDays)),
Result
AS
(SELECT
N AS DateID
,CalendarDate
,DATEPART(DAY, CalendarDate) AS CDay
,DATEPART(MONTH, CalendarDate) AS MonthNo
,DATEPART(YEAR, CalendarDate) AS YearNo
,(DATEPART(WEEKDAY, CalendarDate) + @@DATEFIRST) % 7 AS [DayofWeek]
,CASE
WHEN CONVERT(VARCHAR(10), DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, CalendarDate) + 1, 0)), 111) = CalendarDate THEN 1
ELSE 0
END AS EndOfMonth
,CASE
WHEN DATEPART(DW, CalendarDate) IN (@saturday, @sunday) THEN 1
ELSE 0
END AS WeekEnd
,CASE
WHEN h.TheDate IS NOT NULL THEN 1
ELSE 0
END AS Holiday
,CASE
WHEN DATEPART(DW, CalendarDate) NOT IN (@saturday, @sunday) AND
h.TheDate IS NULL THEN 1
ELSE 0
END AS WorkDay
FROM Dates AS d
LEFT JOIN Calendar.Holidays(@fromDate, DATEADD(DAY, 1, @toDate)) AS h
ON d.CalendarDate = h.TheDate)
INSERT INTO @utCalendar
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY WorkDay ORDER BY CalendarDate)
FROM Result;
RETURN;
END;
GO