Building calendar table using MTVF

,

SELECT [Calendar].[DetermineEaster](2016,0) ->Corpus Christ

SELECT [Calendar].[DetermineEaster](2016,1) ->Easter

SELECT dateAdd(day,-46,[Calendar].[DetermineEaster](2016,1)) --Powdery mildew

SELECT dateAdd(day,-7,[Calendar].[DetermineEaster](2016,1)) --Palm Sunday

SELECT

*

FROM CALENDAR.EASTERS(2017, 2018);

TheYear     TheEaster  TheCorpusChrist

----------- ---------- ---------------

2017        2017-04-16 2017-06-15

2018        2018-04-01 2018-05-31

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

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

/*
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

Rate

4.22 (9)

Share

Share

Rate

4.22 (9)