CreateCalendarTable.sql

--Adapted from http://www.chilledsql.com/welcome/tip_category_dateandtime/tip_detail_dateandtime_createmastercalendartable
--If this is an existing table, do not drop/recreate the table, just adjust dates and run the appropriate population script
--Adjust code to ignore dates already in the calendar table.


SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET ARITHIGNORE ON

----------------------------------------------------------------------------------------------------------------------
--	Error Trapping: Check If Permanent Table(s) Already Exist(s) And Drop If Applicable
----------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID ('Calendar') IS NOT NULL
BEGIN

	DROP TABLE Calendar

END


----------------------------------------------------------------------------------------------------------------------
--	Permanent Table: Create Date Xref Table
----------------------------------------------------------------------------------------------------------------------

CREATE TABLE Calendar

	(
		 Calendar_Date					DATE CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED
		,Calendar_Year					INT
		,Calendar_Month					INT
		,Calendar_Day					INT
		,Calendar_Day_Suffix			VARCHAR(4)
		,Calendar_Quarter				INT
		,Calendar_Quarter_Name			VARCHAR(10)
		,First_Day_in_Week				DATETIME
		,Last_Day_in_Week				DATETIME
		,Is_Week_in_Same_Month			INT
		,First_Day_in_Month				DATETIME
		,Last_Day_in_Month				DATETIME
		,Is_Last_Day_in_Month			INT
		,First_Day_in_Quarter			DATETIME
		,Last_Day_in_Quarter			DATETIME
		,Is_Last_Day_in_Quarter			INT		
		,Day_of_Week					INT
		,Day_of_Year					INT
		,Week_of_Month					INT
		,Week_of_Quarter				TINYINT
		,Week_of_Year					INT
		,Days_in_Month					INT
		,Month_Days_Remaining			INT
		,WeekDays_in_Month				INT
		,Month_Weekdays_Remaining		INT
		,Month_WeekDays_Completed		INT
		,Days_in_Quarter				TINYINT
		,Quarter_Days_Remaining			TINYINT
		,Quarter_Days_Completed			TINYINT
		,WeekDays_in_Quarter			TINYINT
		,Quarter_Weekdays_Remaining		TINYINT
		,Quarter_Weekdays_Completed		TINYINT
		,Year_Days_Remaining			INT
		,Is_WeekDay						INT
		,Is_Leap_Year					INT
		,Day_Name						VARCHAR (10)
		,Month_Day_Name_Instance		INT
		,Quarter_Day_Name_Instance		INT
		,Year_Day_Name_Instance			INT
		,Month_Name						VARCHAR (10)
		,Year_Week						VARCHAR (6)
		,Year_Month						VARCHAR (6)
		,Year_Quarter					VARCHAR (6)
		,Is_Holiday_USA					INT
		,Is_Holiday_CANADA				INT
		,Holiday_Name					VARCHAR (50)
	);
GO

CREATE NONCLUSTERED INDEX [IX_Last_Day_in_Week] ON Calendar (Last_Day_in_Week)
CREATE NONCLUSTERED INDEX [IX_Year_Month] ON Calendar (Year_Month)
CREATE NONCLUSTERED INDEX [IX_Year_Quarter] ON Calendar (Year_Quarter)
CREATE NONCLUSTERED INDEX [IX_Calendar_Year] ON Calendar (Calendar_Year)
CREATE NONCLUSTERED INDEX [IX_Calendar_Month] ON Calendar (Calendar_Month)
CREATE NONCLUSTERED INDEX [IX_Calendar_Quarter] ON Calendar (Calendar_Quarter)
CREATE NONCLUSTERED INDEX [IX_First_Day_in_Week] ON Calendar (First_Day_in_Week)
CREATE NONCLUSTERED INDEX [IX_Day_of_Week] ON Calendar (Day_of_Week)
CREATE NONCLUSTERED INDEX [IX_Is_WeekDay] ON Calendar (Is_WeekDay)

ExampleQueries.sql

USE WideWorldImporters
GO

--Test query to get some invoice data
select c.Calendar_Year, c.Calendar_Month
, SUM(il.ExtendedPrice) as TotalInvoiceAmount
, SUM(il.TaxAmount) as TotalTaxAmount
, SUM(il.LineProfit) as TotalLineProfit
from Sales.Invoices as i
JOIN Sales.InvoiceLines as il
	ON i.InvoiceID = il.InvoiceID
JOIN Calendar as c
	ON i.InvoiceDate = c.Calendar_Date
GROUP BY c.Calendar_Year, c.Calendar_Month
ORDER BY c.Calendar_Year, c.Calendar_Month

--Similar Query, but broken down into whether the invoice was on a weekday or not
select c.Calendar_Year, c.Calendar_Month, c.Is_WeekDay
, SUM(il.ExtendedPrice) as TotalInvoiceAmount
, SUM(il.TaxAmount) as TotalTaxAmount
, SUM(il.LineProfit) as TotalLineProfit
from Sales.Invoices as i
JOIN Sales.InvoiceLines as il
	ON i.InvoiceID = il.InvoiceID
JOIN Calendar as c
	ON i.InvoiceDate = c.Calendar_Date
GROUP BY c.Calendar_Year, c.Calendar_Month, c.Is_WeekDay
ORDER BY c.Calendar_Year, c.Calendar_Month

PopulateCalendarTable.sql

CREATE PROC PopulateCalendarTable
  @vDate_Start DATETIME,
  @vDate_End AS DATETIME
AS
BEGIN

--DECLARE @vDate_Start AS DATETIME
--DECLARE @vDate_End AS DATETIME
--SET @vDate_Start = '01/01/2000'
--SET @vDate_End = '12/31/2030'


----------------------------------------------------------------------------------------------------------------------
--	Table Insert: Populate Base Date Values Into Permanent Table Using Common Table Expression (CTE)
----------------------------------------------------------------------------------------------------------------------

WITH cte_date_base_table AS
	(
		SELECT
			@vDate_Start AS Calendar_Date

		UNION ALL

		SELECT
			DATEADD (Day, 1, CTE.Calendar_Date)
		FROM
			cte_date_base_table CTE
		WHERE
			DATEADD (Day, 1, CTE.Calendar_Date) <= @vDate_End
	)

INSERT INTO Calendar
	(
		Calendar_Date
	)

SELECT
	CTE.Calendar_Date
FROM
	cte_date_base_table CTE
WHERE NOT EXISTS (select Calendar_Date FROM Calendar WHERE Calendar_Date = CTE.Calendar_Date)
OPTION
	(MAXRECURSION 0)


----------------------------------------------------------------------------------------------------------------------
--	Table Update I: Populate Additional Date Xref Table Fields (Pass I)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	Calendar
SET
	 Calendar_Year = DATEPART (YEAR, Calendar_Date)
	,Calendar_Month = DATEPART (MONTH, Calendar_Date)
	,Calendar_Day = DATEPART (Day, Calendar_Date)
	,Calendar_Day_Suffix =	CASE
								WHEN DATEPART(Day,Calendar_Date) IN (1, 21, 31) THEN 'st'
								WHEN DATEPART(Day,Calendar_Date) IN (2, 22) THEN 'nd'
								WHEN DATEPART(Day,Calendar_Date) IN (3, 23) THEN 'rd'
								ELSE 'th'
							END
	,Calendar_Quarter = DATEPART (QUARTER, Calendar_Date)
	,Calendar_Quarter_Name =	CASE DATEPART (QUARTER, Calendar_Date)
									WHEN 1 THEN 'First'
									WHEN 2 THEN 'Second'
									WHEN 3 THEN 'Third'
									WHEN 4 THEN 'Fourth'
								END
	,First_Day_in_Week = DATEADD (Day, -DATEPART (WeekDay, Calendar_Date)+1, Calendar_Date)
	,First_Day_in_Month = CONVERT (VARCHAR (6), Calendar_Date, 112)+'01'
	,Day_of_Week = DATEPART (WeekDay, Calendar_Date)
	,Week_of_Year = DATEPART (Week, Calendar_Date)
	,Day_of_Year = DATEPART (DayOFYEAR, Calendar_Date)
	,Is_WeekDay = ISNULL ((CASE
								WHEN ((@@DATEFIRST-1)+(DATEPART (WeekDay, Calendar_Date)-1))%7 NOT IN (5,6) THEN 1
								END),0)
	,Day_Name = DATENAME (WeekDay, Calendar_Date)
	,Month_Name = DATENAME (MONTH, Calendar_Date)

----------------------------------------------------------------------------------------------------------------------
--	Table Update II: Populate Additional Date Xref Table Fields (Pass II)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	Calendar
SET
	 Last_Day_in_Week = First_Day_in_Week+6
	,Last_Day_in_Month = DATEADD (MONTH, 1, First_Day_in_Month)-1
	,First_Day_in_Quarter = A.First_Day_in_Quarter
	,Last_Day_in_Quarter = A.Last_Day_in_Quarter
	,Week_of_Month = DATEDIFF (Week, First_Day_in_Month, Calendar_Date)+1
	,Week_of_Quarter = (week_of_year-A.min_week_of_year_in_quarter)+1
	,Is_Leap_Year = ISNULL ((	CASE
									WHEN Calendar_Year%400 = 0 THEN 1
									WHEN Calendar_Year%100 = 0 THEN 0
									WHEN Calendar_Year%4 = 0 THEN 1
								END
							),0)
	,Year_Week = CONVERT (VARCHAR (4), Calendar_Year)+RIGHT ('0'+CONVERT (VARCHAR (2), Week_of_Year),2)
	,Year_Month = CONVERT (VARCHAR (4), Calendar_Year)+RIGHT ('0'+CONVERT (VARCHAR (2), Calendar_Month),2)
	,Year_Quarter = CONVERT (VARCHAR (4), Calendar_Year)+'Q'+CONVERT (VARCHAR (1), Calendar_Quarter)
FROM

	(
		SELECT
			 X.calendar_year AS subquery_calendar_year
			,X.calendar_quarter AS subquery_calendar_quarter
			,MIN (X.calendar_date) AS First_Day_in_Quarter
			,MAX (X.calendar_date) AS Last_Day_in_Quarter
			,MIN (X.week_of_year) AS min_week_of_year_in_quarter
		FROM
			Calendar X
		GROUP BY
			 X.calendar_year
			,X.calendar_quarter
	) A

WHERE
	A.subquery_calendar_year = calendar_year
	AND A.subquery_calendar_quarter = calendar_quarter

----------------------------------------------------------------------------------------------------------------------
--	Table Update III: Populate Additional Date Xref Table Fields (Pass III)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	Calendar
SET
	 Is_Last_Day_in_Month = (CASE
								WHEN last_day_in_month = calendar_date THEN 1
								ELSE 0
								END)
	,Is_Last_Day_in_Quarter = (CASE
									WHEN Last_Day_in_Quarter = calendar_date THEN 1
									ELSE 0
									END)
	,Days_in_Month = DATEPART (Day, Last_Day_in_Month)
	,Days_in_Quarter = DATEDIFF (DAY, First_Day_in_Quarter, Last_Day_in_Quarter)+1
	,WeekDays_in_Month = A.WeekDays_in_Month
	,Quarter_Days_Remaining = DATEDIFF (DAY, calendar_date, Last_Day_in_Quarter)
	,WeekDays_in_Quarter = B.WeekDays_in_Quarter
	,Year_Days_Remaining = (365+Is_Leap_Year)-Day_of_Year
FROM

	(
		SELECT
			 X.Year_Month
			,SUM (X.Is_WeekDay) AS WeekDays_in_Month
		FROM
			Calendar X
		GROUP BY
			X.Year_Month
	) AS A
	,(
		SELECT
			 X.Year_Quarter AS Subquery_Year_Quarter
			,SUM (X.is_weekday) AS WeekDays_in_Quarter
		FROM
			Calendar X
		GROUP BY
			X.Year_Quarter
	 ) B

WHERE
	A.Year_Month = Calendar.Year_Month
	AND B.Subquery_Year_Quarter = Year_Quarter


----------------------------------------------------------------------------------------------------------------------
--	Table Update IV: Populate Additional Date Xref Table Fields (Pass IV)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	Calendar
SET
	Month_Weekdays_Remaining = WeekDays_in_Month-A.Month_Weekdays_Remaining_subtraction
	,Quarter_Weekdays_Remaining = WeekDays_in_Quarter-A.Quarter_Weekdays_Remaining_subtraction
FROM
	(
		SELECT
			 X.calendar_date AS subquery_calendar_date
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										X.year_month
									ORDER BY
										X.calendar_date
								) AS Month_Weekdays_Remaining_subtraction
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										X.Year_Quarter
									ORDER BY
										X.calendar_date
								) AS Quarter_Weekdays_Remaining_subtraction
		FROM
			Calendar X
		WHERE
			X.is_weekday = 1
	) A

WHERE
	A.subquery_calendar_date = calendar_date


----------------------------------------------------------------------------------------------------------------------
--	Table Update V: Populate Additional Date Xref Table Fields (Pass V)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	Calendar
SET
	 month_weekdays_remaining = A.month_weekdays_remaining
	,Quarter_Weekdays_Remaining = A.Quarter_Weekdays_Remaining
FROM

	(
		SELECT
			 X.calendar_date AS subquery_calendar_date
			,COALESCE (Y.month_weekdays_remaining, Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
			,COALESCE (Y.Quarter_Weekdays_Remaining, Z.Quarter_Weekdays_Remaining, X.WeekDays_in_Quarter) AS Quarter_Weekdays_Remaining
		FROM
			Calendar X
			LEFT JOIN Calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
				AND Y.year_month = X.year_month
			LEFT JOIN Calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
				AND Z.year_month = X.year_month
		WHERE
			X.month_weekdays_remaining IS NULL
	) A

WHERE
	A.subquery_calendar_date = calendar_date

----------------------------------------------------------------------------------------------------------------------
--	Table Update VI: Populate Additional Date Xref Table Fields (Pass VI)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	Calendar
SET
	 Is_Week_in_Same_Month = A.Is_Week_in_Same_Month
	,Month_Days_Remaining = Days_in_Month-Calendar_Day
	,Month_WeekDays_Completed = WeekDays_in_Month-Month_Weekdays_Remaining
	,Quarter_Days_Completed = Days_in_Quarter-Quarter_Days_Remaining
	,Quarter_Weekdays_Completed = WeekDays_in_Quarter-Quarter_Weekdays_Remaining
	,Month_Day_Name_Instance = A.Month_Day_Name_Instance
	,Quarter_Day_Name_Instance = A.Quarter_Day_Name_Instance
	,Year_Day_Name_Instance = A.Year_Day_Name_Instance
FROM

	(
		SELECT
			 X.Calendar_Date
			,ISNULL ((CASE
						WHEN DATEDIFF (MONTH, X.First_Day_in_Week, X.Last_Day_in_Week) = 0 THEN 1
						END),0) AS Is_Week_in_Same_Month
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										 X.Year_Month
										,X.Day_Name
									ORDER BY
										X.Calendar_Date
								) AS Month_Day_Name_Instance
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										 X.Year_Quarter
										,X.Day_Name
									ORDER BY
										X.Calendar_Date
								) AS Quarter_Day_Name_Instance
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										 X.Calendar_Year
										,X.Day_Name
									ORDER BY
										X.Calendar_Date
								) AS Year_Day_Name_Instance
		FROM
			Calendar X
	) A

WHERE
	A.Calendar_Date = Calendar.Calendar_Date


----------------------------------------------------------------------------------------------------------------------
--	Table Update VI: Populate Holiday Fields (Pass VII)
----------------------------------------------------------------------------------------------------------------------

--THANKSGIVING --------------------------------------------------------------------------------------------------------------
--Fourth THURSDay in November. (USA)
UPDATE Calendar
SET Holiday_Name = 'Thanksgiving Day (USA)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 11 
 AND Day_Name = 'Thursday'
 AND Month_Day_Name_Instance = 4


--Second Monday in October. (CANADA)
UPDATE Calendar
SET Holiday_Name = 'Thanksgiving Day (CANADA)'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 11 
 AND Day_Name = 'Monday' 
 AND Month_Day_Name_Instance = 2



--CHRISTMAS -------------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Christmas Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 12 
AND Calendar_Day = 25


;WITH ChristmasOnSunday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 25
	AND Day_Name = 'Sunday'
)
UPDATE Calendar
SET Holiday_Name = 'Christmas Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN ChristmasOnSunday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 26

;WITH ChristmasOnSaturday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 25
	AND Day_Name = 'Saturday'
)
UPDATE Calendar
SET Holiday_Name = 'Christmas Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN ChristmasOnSaturday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 27


--BOXING DAY -------------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Boxing Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 12 
AND Calendar_Day = 26


;WITH BoxingDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 26
	AND Day_Name = 'Sunday'
)
UPDATE Calendar
SET Holiday_Name = 'Boxing Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN BoxingDayOnSunday bd
	ON c.Calendar_Year = bd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 28


;WITH BoxingDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 26
	AND Day_Name = 'Saturday'
)
UPDATE Calendar
SET Holiday_Name = 'Boxing Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN BoxingDayOnSaturday bd
	ON c.Calendar_Year = bd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 28


;WITH BoxingDayOnMonday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 26
	AND Day_Name = 'Monday'
)
UPDATE Calendar
SET Holiday_Name = 'Boxing Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN BoxingDayOnMonday bd
	ON c.Calendar_Year = bd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 27


--4th of July ---------------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Independance Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 7 AND Calendar_Day = 4


-- New Years Day ---------------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'New Year''s Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 1 AND Calendar_Day = 1


;WITH NewYearsDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'January'
	AND Calendar_Day = 1
	AND Day_Name = 'Sunday'
)
UPDATE Calendar
SET Holiday_Name = 'New Year''s Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN NewYearsDayOnSunday nyd
	ON c.Calendar_Year = nyd.Calendar_Year
WHERE Calendar_Month = 1 
AND Calendar_Day = 2


;WITH NewYearsDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'January'
	AND Calendar_Day = 1
	AND Day_Name = 'Saturday'
)
UPDATE Calendar
SET Holiday_Name = 'New Year''s Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN NewYearsDayOnSaturday nyd
	ON c.Calendar_Year = nyd.Calendar_Year
WHERE Calendar_Month = 1 
AND Calendar_Day = 3



--Memorial Day ----------------------------------------------------------------------------------------
--Last MonDay in May
;WITH LastMondayInMay AS
(
	SELECT	Calendar_Year
			,MAX(Month_Day_Name_Instance) AS LastMonday
	FROM Calendar
	WHERE Month_Name = 'May'
	AND Day_Name = 'Monday'
	GROUP BY Calendar_Year
)
UPDATE Calendar
SET Holiday_Name = 'Memorial Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
FROM Calendar c
INNER JOIN LastMondayInMay lm
	ON c.Calendar_Year = lm.Calendar_Year
	AND c.Month_Day_Name_Instance = lm.LastMonday
WHERE c.Month_Name =  'May'
AND c.Day_Name = 'Monday'
 
 
--Labor Day -------------------------------------------------------------------------------------------
--First Monday in September
UPDATE Calendar
SET Holiday_Name = 'Labor Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar
WHERE Month_Name = 'September'
AND Day_Name = 'Monday'
AND Month_Day_Name_Instance = 1


-- Valentine's Day ---------------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Valentine''s Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 2 AND Calendar_Day = 14


-- Saint Patrick's Day -----------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Saint Patrick''s Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 3 AND Calendar_Day = 17


--Martin Luthor King Day ---------------------------------------------------------------------------------------
--Third MonDay in January starting in 1983
UPDATE Calendar
SET Holiday_Name = 'Martin Luthor King Jr Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 1--January
 AND Day_Name = 'MonDay'
 AND Calendar_Year >= 1983--When Holiday was official
 AND Month_Day_Name_Instance = 3--Third X Day of current month.


--President's Day ---------------------------------------------------------------------------------------
--Third MonDay in February.
UPDATE Calendar
SET Holiday_Name = 'President''s Day'--select * from dim_date
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 2--February
 AND Day_Name = 'MonDay'
 AND Month_Day_Name_Instance = 3--Third occurance of a monDay in this month.


--Mother's Day ---------------------------------------------------------------------------------------
--Second SunDay of May
UPDATE Calendar
SET Holiday_Name = 'Mother''s Day'--select * from dim_date
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 5--May
 AND Day_Name = 'SunDay'
 AND Month_Day_Name_Instance = 2--Second occurance of a monDay in this month.


--Father's Day ---------------------------------------------------------------------------------------
--Third SunDay of June
UPDATE Calendar
SET Holiday_Name = 'Father''s Day'--select * from dim_date
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 6--June
 AND Day_Name = 'SunDay'
 AND Month_Day_Name_Instance = 3--Third occurance of a monDay in this month.


--Halloween 10/31 ----------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Halloween'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 10 AND Calendar_Day = 31


--Victoria Day  ----------------------------------------------------------------------------------
-- second last Monday in May
;WITH SecondLastMondayInMay AS
(
	SELECT	Calendar_Year
			,(MAX(Month_Day_Name_Instance) - 1) AS SecondLastMonday
	FROM Calendar
	WHERE Month_Name = 'May'
	AND Day_Name = 'Monday'
	GROUP BY Calendar_Year
)
UPDATE Calendar
SET Holiday_Name = 'Victoria Day'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN SecondLastMondayInMay lm
	ON c.Calendar_Year = lm.Calendar_Year
	AND c.Month_Day_Name_Instance = lm.SecondLastMonday
WHERE c.Month_Name =  'May'
AND c.Day_Name = 'Monday'
 
 


--Remembrance Day -------------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Remembrance Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 11 
AND Calendar_Day = 11


;WITH RemembranceDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'November'
	AND Calendar_Day = 11
	AND Day_Name = 'Sunday'
)
UPDATE Calendar
SET Holiday_Name = 'Remembrance Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN RemembranceDayOnSunday rd
	ON c.Calendar_Year = rd.Calendar_Year
WHERE Calendar_Month = 11 
AND Calendar_Day = 12


;WITH RemembranceDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'November'
	AND Calendar_Day = 11
	AND Day_Name = 'Saturday'
)
UPDATE Calendar
SET Holiday_Name = 'Remembrance Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN RemembranceDayOnSaturday rd
	ON c.Calendar_Year = rd.Calendar_Year
WHERE Calendar_Month = 11 
AND Calendar_Day = 13



--Canada Day -------------------------------------------------------------------------------------------
UPDATE Calendar
SET Holiday_Name = 'Canada Day'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 7 
AND Calendar_Day = 1

;WITH CanadaDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'July'
	AND Calendar_Day = 1
	AND Day_Name = 'Sunday'
)
UPDATE Calendar
SET Holiday_Name = 'Canada Day (in Lieu)'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN CanadaDayOnSunday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 7 
AND Calendar_Day = 2



;WITH CanadaDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM Calendar
	WHERE Month_Name = 'July'
	AND Calendar_Day = 1
	AND Day_Name = 'Saturday'
)
UPDATE Calendar
SET Holiday_Name = 'Canada Day (in Lieu)'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
FROM Calendar c
INNER JOIN CanadaDayOnSaturday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 7 
AND Calendar_Day = 3



----Easter Sunday -------------------------------------------------------------------------------------------
--;WITH Years AS
--(
--	SELECT DISTINCT Calendar_Year
--	FROM Calendar
--)
--UPDATE c
--SET Holiday_Name = 'Easter Sunday'
--	,Is_Holiday_USA = 1
--	,Is_Holiday_CANADA = 1
--FROM Calendar c
--INNER JOIN Years y 
--	ON c.Calendar_Date= dbo.udf_EasterSundayByYear(y.Calendar_Year)


----Good Friday -------------------------------------------------------------------------------------------
--;WITH Years AS
--(
--	SELECT DISTINCT Calendar_Year
--	FROM Calendar
--)
--UPDATE c
--SET Holiday_Name = 'Good Friday'
--	,Is_Holiday_USA = 1
--	,Is_Holiday_CANADA = 1
--FROM Calendar c
--INNER JOIN Years y 
--	ON c.Calendar_Date= DATEADD(dd, -2, dbo.udf_EasterSundayByYear(y.Calendar_Year))


--Civic Holiday ---------------------------------------------------------------------------------------
--First Monday of August
UPDATE Calendar
SET Holiday_Name = 'Civic Holiday'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 8--June
 AND Day_Name = 'Monday'
 AND Month_Day_Name_Instance = 1--First occurance of a monDay in this month.


END

udf_EasterSundayByYear

DROP FUNCTION [dbo].[udf_EasterSundayByYear]
GO

CREATE FUNCTION [dbo].[udf_EasterSundayByYear]
(@Year CHAR(4))
RETURNS SMALLDATETIME
AS
BEGIN
--http://aa.usno.navy.mil/faq/docs/easter.php
   DECLARE
       @c INT
       , @n INT
       , @k INT
       , @i INT
       , @j INT
       , @l INT
       , @m INT
       , @d INT
       , @Easter DATETIME

   SET @c = (@Year / 100)
   SET @n = @Year - 19 * (@Year / 19)
   SET @k = (@c - 17) / 25
   SET @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15
   SET @i = @i - 30 * ( @i / 30 )
   SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
   SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
   SET @j = @j - 7 * (@j / 7)
   SET @l = @i - @j
   SET @m = 3 + (@l + 40) / 44
   SET @d = @l + 28 - 31 * ( @m / 4 )

   SET @Easter = (SELECT RIGHT('0' + CONVERT(VARCHAR(2),@m),2) + '/' + RIGHT('0' + CONVERT(VARCHAR(2),@d),2) + '/' + CONVERT(CHAR(4),@Year))

   RETURN @Easter
END 

z - Create and Populate Calendar Table

--Adapted from http://www.chilledsql.com/welcome/tip_category_dateandtime/tip_detail_dateandtime_createmastercalendartable
--If this is an existing table, do not drop/recreate the table, just adjust dates and run the appropriate population script
--Adjust code to ignore dates already in the calendar table.


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON


DECLARE @vDate_Start AS DATETIME
DECLARE @vDate_End AS DATETIME


SET @vDate_Start = '01/01/2000'
SET @vDate_End = '12/31/2030'


----------------------------------------------------------------------------------------------------------------------
--	Error Trapping: Check If Permanent Table(s) Already Exist(s) And Drop If Applicable
----------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID ('#Calendar') IS NOT NULL
BEGIN

	DROP TABLE #Calendar

END


----------------------------------------------------------------------------------------------------------------------
--	Permanent Table: Create Date Xref Table
----------------------------------------------------------------------------------------------------------------------

CREATE TABLE #Calendar

	(
		 Calendar_Date					DATE CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED
		,Calendar_Year					INT
		,Calendar_Month					INT
		,Calendar_Day					INT
		,Calendar_Day_Suffix			VARCHAR(4)
		,Calendar_Quarter				INT
		,Calendar_Quarter_Name			VARCHAR(10)
		,First_Day_in_Week				DATETIME
		,Last_Day_in_Week				DATETIME
		,Is_Week_in_Same_Month			INT
		,First_Day_in_Month				DATETIME
		,Last_Day_in_Month				DATETIME
		,Is_Last_Day_in_Month			INT
		,First_Day_in_Quarter			DATETIME
		,Last_Day_in_Quarter			DATETIME
		,Is_Last_Day_in_Quarter			INT		
		,Day_of_Week					INT
		,Day_of_Year					INT
		,Week_of_Month					INT
		,Week_of_Quarter				TINYINT
		,Week_of_Year					INT
		,Days_in_Month					INT
		,Month_Days_Remaining			INT
		,WeekDays_in_Month				INT
		,Month_Weekdays_Remaining		INT
		,Month_WeekDays_Completed		INT
		,Days_in_Quarter				TINYINT
		,Quarter_Days_Remaining			TINYINT
		,Quarter_Days_Completed			TINYINT
		,WeekDays_in_Quarter			TINYINT
		,Quarter_Weekdays_Remaining		TINYINT
		,Quarter_Weekdays_Completed		TINYINT
		,Year_Days_Remaining			INT
		,Is_WeekDay						INT
		,Is_Leap_Year					INT
		,Day_Name						VARCHAR (10)
		,Month_Day_Name_Instance		INT
		,Quarter_Day_Name_Instance		INT
		,Year_Day_Name_Instance			INT
		,Month_Name						VARCHAR (10)
		,Year_Week						VARCHAR (6)
		,Year_Month						VARCHAR (6)
		,Year_Quarter					VARCHAR (6)
		,Is_Holiday_USA					INT
		,Is_Holiday_CANADA				INT
		,Holiday_Name					VARCHAR (50)
	);


----------------------------------------------------------------------------------------------------------------------
--	Table Insert: Populate Base Date Values Into Permanent Table Using Common Table Expression (CTE)
----------------------------------------------------------------------------------------------------------------------

WITH cte_date_base_table AS

	(
		SELECT
			@vDate_Start AS Calendar_Date

		UNION ALL

		SELECT
			DATEADD (Day, 1, CTE.Calendar_Date)
		FROM
			cte_date_base_table CTE
		WHERE
			DATEADD (Day, 1, CTE.Calendar_Date) <= @vDate_End
	)

INSERT INTO #Calendar

	(
		Calendar_Date
	)

SELECT
	CTE.Calendar_Date
FROM
	cte_date_base_table CTE
OPTION
	(MAXRECURSION 0)


----------------------------------------------------------------------------------------------------------------------
--	Table Update I: Populate Additional Date Xref Table Fields (Pass I)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	#Calendar
SET
	 Calendar_Year = DATEPART (YEAR, Calendar_Date)
	,Calendar_Month = DATEPART (MONTH, Calendar_Date)
	,Calendar_Day = DATEPART (Day, Calendar_Date)
	,Calendar_Day_Suffix =	CASE
								WHEN DATEPART(Day,Calendar_Date) IN (1, 21, 31) THEN 'st'
								WHEN DATEPART(Day,Calendar_Date) IN (2, 22) THEN 'nd'
								WHEN DATEPART(Day,Calendar_Date) IN (3, 23) THEN 'rd'
								ELSE 'th'
							END
	,Calendar_Quarter = DATEPART (QUARTER, Calendar_Date)
	,Calendar_Quarter_Name =	CASE DATEPART (QUARTER, Calendar_Date)
									WHEN 1 THEN 'First'
									WHEN 2 THEN 'Second'
									WHEN 3 THEN 'Third'
									WHEN 4 THEN 'Fourth'
								END
	,First_Day_in_Week = DATEADD (Day, -DATEPART (WeekDay, Calendar_Date)+1, Calendar_Date)
	,First_Day_in_Month = CONVERT (VARCHAR (6), Calendar_Date, 112)+'01'
	,Day_of_Week = DATEPART (WeekDay, Calendar_Date)
	,Week_of_Year = DATEPART (Week, Calendar_Date)
	,Day_of_Year = DATEPART (DayOFYEAR, Calendar_Date)
	,Is_WeekDay = ISNULL ((CASE
								WHEN ((@@DATEFIRST-1)+(DATEPART (WeekDay, Calendar_Date)-1))%7 NOT IN (5,6) THEN 1
								END),0)
	,Day_Name = DATENAME (WeekDay, Calendar_Date)
	,Month_Name = DATENAME (MONTH, Calendar_Date)


ALTER TABLE #Calendar ALTER COLUMN Calendar_Year INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Calendar_Month INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Calendar_Day INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Calendar_Quarter INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN First_Day_in_Week DATETIME NOT NULL
ALTER TABLE #Calendar ALTER COLUMN First_Day_in_Month DATETIME NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Day_of_Week INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Week_of_Year INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Day_of_Year INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Is_WeekDay INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Day_Name VARCHAR (10) NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Month_Name VARCHAR (10) NOT NULL


CREATE NONCLUSTERED INDEX [IX_Calendar_Year] ON #Calendar (Calendar_Year)
CREATE NONCLUSTERED INDEX [IX_Calendar_Month] ON #Calendar (Calendar_Month)
CREATE NONCLUSTERED INDEX [IX_Calendar_Quarter] ON #Calendar (Calendar_Quarter)
CREATE NONCLUSTERED INDEX [IX_First_Day_in_Week] ON #Calendar (First_Day_in_Week)
CREATE NONCLUSTERED INDEX [IX_Day_of_Week] ON #Calendar (Day_of_Week)
CREATE NONCLUSTERED INDEX [IX_Is_WeekDay] ON #Calendar (Is_WeekDay)


----------------------------------------------------------------------------------------------------------------------
--	Table Update II: Populate Additional Date Xref Table Fields (Pass II)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	#Calendar
SET
	 Last_Day_in_Week = First_Day_in_Week+6
	,Last_Day_in_Month = DATEADD (MONTH, 1, First_Day_in_Month)-1
	,First_Day_in_Quarter = A.First_Day_in_Quarter
	,Last_Day_in_Quarter = A.Last_Day_in_Quarter
	,Week_of_Month = DATEDIFF (Week, First_Day_in_Month, Calendar_Date)+1
	,Week_of_Quarter = (week_of_year-A.min_week_of_year_in_quarter)+1
	,Is_Leap_Year = ISNULL ((	CASE
									WHEN Calendar_Year%400 = 0 THEN 1
									WHEN Calendar_Year%100 = 0 THEN 0
									WHEN Calendar_Year%4 = 0 THEN 1
								END
							),0)
	,Year_Week = CONVERT (VARCHAR (4), Calendar_Year)+RIGHT ('0'+CONVERT (VARCHAR (2), Week_of_Year),2)
	,Year_Month = CONVERT (VARCHAR (4), Calendar_Year)+RIGHT ('0'+CONVERT (VARCHAR (2), Calendar_Month),2)
	,Year_Quarter = CONVERT (VARCHAR (4), Calendar_Year)+'Q'+CONVERT (VARCHAR (1), Calendar_Quarter)
FROM

	(
		SELECT
			 X.calendar_year AS subquery_calendar_year
			,X.calendar_quarter AS subquery_calendar_quarter
			,MIN (X.calendar_date) AS First_Day_in_Quarter
			,MAX (X.calendar_date) AS Last_Day_in_Quarter
			,MIN (X.week_of_year) AS min_week_of_year_in_quarter
		FROM
			#Calendar X
		GROUP BY
			 X.calendar_year
			,X.calendar_quarter
	) A

WHERE
	A.subquery_calendar_year = calendar_year
	AND A.subquery_calendar_quarter = calendar_quarter


ALTER TABLE #Calendar ALTER COLUMN Last_Day_in_Week DATETIME NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Last_Day_in_Month DATETIME NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Last_Day_in_Week DATETIME NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Week_of_Month INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Week_of_Quarter INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Is_Leap_Year INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Year_Week VARCHAR (6) NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Year_Month VARCHAR (6) NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Year_Quarter VARCHAR (6) NOT NULL


CREATE NONCLUSTERED INDEX [IX_Last_Day_in_Week] ON #Calendar (Last_Day_in_Week)
CREATE NONCLUSTERED INDEX [IX_Year_Month] ON #Calendar (Year_Month)
CREATE NONCLUSTERED INDEX [IX_Year_Quarter] ON #Calendar (Year_Quarter)


----------------------------------------------------------------------------------------------------------------------
--	Table Update III: Populate Additional Date Xref Table Fields (Pass III)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	#Calendar
SET
	 Is_Last_Day_in_Month = (CASE
								WHEN last_day_in_month = calendar_date THEN 1
								ELSE 0
								END)
	,Is_Last_Day_in_Quarter = (CASE
									WHEN Last_Day_in_Quarter = calendar_date THEN 1
									ELSE 0
									END)
	,Days_in_Month = DATEPART (Day, Last_Day_in_Month)
	,Days_in_Quarter = DATEDIFF (DAY, First_Day_in_Quarter, Last_Day_in_Quarter)+1
	,WeekDays_in_Month = A.WeekDays_in_Month
	,Quarter_Days_Remaining = DATEDIFF (DAY, calendar_date, Last_Day_in_Quarter)
	,WeekDays_in_Quarter = B.WeekDays_in_Quarter
	,Year_Days_Remaining = (365+Is_Leap_Year)-Day_of_Year
FROM

	(
		SELECT
			 X.Year_Month
			,SUM (X.Is_WeekDay) AS WeekDays_in_Month
		FROM
			#Calendar X
		GROUP BY
			X.Year_Month
	) AS A
	,(
		SELECT
			 X.Year_Quarter AS Subquery_Year_Quarter
			,SUM (X.is_weekday) AS WeekDays_in_Quarter
		FROM
			#Calendar X
		GROUP BY
			X.Year_Quarter
	 ) B

WHERE
	A.Year_Month = #Calendar.Year_Month
	AND B.Subquery_Year_Quarter = Year_Quarter


ALTER TABLE #Calendar ALTER COLUMN Is_Last_Day_in_Month INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Is_Last_Day_in_Quarter INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Days_in_Month INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN WeekDays_in_Month INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Days_in_Quarter INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Quarter_Days_Remaining INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN WeekDays_in_Quarter INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Year_Days_Remaining INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
--	Table Update IV: Populate Additional Date Xref Table Fields (Pass IV)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	#Calendar
SET
	Month_Weekdays_Remaining = WeekDays_in_Month-A.Month_Weekdays_Remaining_subtraction
	,Quarter_Weekdays_Remaining = WeekDays_in_Quarter-A.Quarter_Weekdays_Remaining_subtraction
FROM
	(
		SELECT
			 X.calendar_date AS subquery_calendar_date
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										X.year_month
									ORDER BY
										X.calendar_date
								) AS Month_Weekdays_Remaining_subtraction
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										X.Year_Quarter
									ORDER BY
										X.calendar_date
								) AS Quarter_Weekdays_Remaining_subtraction
		FROM
			#Calendar X
		WHERE
			X.is_weekday = 1
	) A

WHERE
	A.subquery_calendar_date = calendar_date


----------------------------------------------------------------------------------------------------------------------
--	Table Update V: Populate Additional Date Xref Table Fields (Pass V)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	#Calendar
SET
	 month_weekdays_remaining = A.month_weekdays_remaining
	,Quarter_Weekdays_Remaining = A.Quarter_Weekdays_Remaining
FROM

	(
		SELECT
			 X.calendar_date AS subquery_calendar_date
			,COALESCE (Y.month_weekdays_remaining, Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
			,COALESCE (Y.Quarter_Weekdays_Remaining, Z.Quarter_Weekdays_Remaining, X.WeekDays_in_Quarter) AS Quarter_Weekdays_Remaining
		FROM
			#Calendar X
			LEFT JOIN #Calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
				AND Y.year_month = X.year_month
			LEFT JOIN #Calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
				AND Z.year_month = X.year_month
		WHERE
			X.month_weekdays_remaining IS NULL
	) A

WHERE
	A.subquery_calendar_date = calendar_date


ALTER TABLE #Calendar ALTER COLUMN Month_Weekdays_Remaining INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Quarter_Weekdays_Remaining INT NOT NULL

----------------------------------------------------------------------------------------------------------------------
--	Table Update VI: Populate Additional Date Xref Table Fields (Pass VI)
----------------------------------------------------------------------------------------------------------------------

UPDATE
	#Calendar
SET
	 Is_Week_in_Same_Month = A.Is_Week_in_Same_Month
	,Month_Days_Remaining = Days_in_Month-Calendar_Day
	,Month_WeekDays_Completed = WeekDays_in_Month-Month_Weekdays_Remaining
	,Quarter_Days_Completed = Days_in_Quarter-Quarter_Days_Remaining
	,Quarter_Weekdays_Completed = WeekDays_in_Quarter-Quarter_Weekdays_Remaining
	,Month_Day_Name_Instance = A.Month_Day_Name_Instance
	,Quarter_Day_Name_Instance = A.Quarter_Day_Name_Instance
	,Year_Day_Name_Instance = A.Year_Day_Name_Instance
FROM

	(
		SELECT
			 X.Calendar_Date
			,ISNULL ((CASE
						WHEN DATEDIFF (MONTH, X.First_Day_in_Week, X.Last_Day_in_Week) = 0 THEN 1
						END),0) AS Is_Week_in_Same_Month
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										 X.Year_Month
										,X.Day_Name
									ORDER BY
										X.Calendar_Date
								) AS Month_Day_Name_Instance
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										 X.Year_Quarter
										,X.Day_Name
									ORDER BY
										X.Calendar_Date
								) AS Quarter_Day_Name_Instance
			,ROW_NUMBER () OVER
								(
									PARTITION BY
										 X.Calendar_Year
										,X.Day_Name
									ORDER BY
										X.Calendar_Date
								) AS Year_Day_Name_Instance
		FROM
			#Calendar X
	) A

WHERE
	A.Calendar_Date = #Calendar.Calendar_Date


ALTER TABLE #Calendar ALTER COLUMN Is_Week_in_Same_Month INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Month_Days_Remaining INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Month_WeekDays_Completed INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Quarter_Days_Completed INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Quarter_Weekdays_Completed INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Month_Day_Name_Instance INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Quarter_Day_Name_Instance INT NOT NULL
ALTER TABLE #Calendar ALTER COLUMN Year_Day_Name_Instance INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
--	Table Update VI: Populate Holiday Fields (Pass VII)
----------------------------------------------------------------------------------------------------------------------

--THANKSGIVING --------------------------------------------------------------------------------------------------------------
--Fourth THURSDay in November. (USA)
UPDATE #Calendar
SET Holiday_Name = 'Thanksgiving Day (USA)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 11 
 AND Day_Name = 'Thursday' 
 AND Month_Day_Name_Instance = 4
GO

--Second Monday in October. (CANADA)
UPDATE #Calendar
SET Holiday_Name = 'Thanksgiving Day (CANADA)'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 11 
 AND Day_Name = 'Monday' 
 AND Month_Day_Name_Instance = 2
GO


--CHRISTMAS -------------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Christmas Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 12 
AND Calendar_Day = 25


;WITH ChristmasOnSunday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 25
	AND Day_Name = 'Sunday'
)
UPDATE #Calendar
SET Holiday_Name = 'Christmas Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN ChristmasOnSunday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 26

;WITH ChristmasOnSaturday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 25
	AND Day_Name = 'Saturday'
)
UPDATE #Calendar
SET Holiday_Name = 'Christmas Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN ChristmasOnSaturday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 27


--BOXING DAY -------------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Boxing Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 12 
AND Calendar_Day = 26


;WITH BoxingDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 26
	AND Day_Name = 'Sunday'
)
UPDATE #Calendar
SET Holiday_Name = 'Boxing Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN BoxingDayOnSunday bd
	ON c.Calendar_Year = bd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 28


;WITH BoxingDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 26
	AND Day_Name = 'Saturday'
)
UPDATE #Calendar
SET Holiday_Name = 'Boxing Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN BoxingDayOnSaturday bd
	ON c.Calendar_Year = bd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 28


;WITH BoxingDayOnMonday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'December'
	AND Calendar_Day = 26
	AND Day_Name = 'Monday'
)
UPDATE #Calendar
SET Holiday_Name = 'Boxing Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN BoxingDayOnMonday bd
	ON c.Calendar_Year = bd.Calendar_Year
WHERE Calendar_Month = 12 
AND Calendar_Day = 27


--4th of July ---------------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Independance Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 7 AND Calendar_Day = 4


-- New Years Day ---------------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'New Year''s Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 1 AND Calendar_Day = 1


;WITH NewYearsDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'January'
	AND Calendar_Day = 1
	AND Day_Name = 'Sunday'
)
UPDATE #Calendar
SET Holiday_Name = 'New Year''s Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN NewYearsDayOnSunday nyd
	ON c.Calendar_Year = nyd.Calendar_Year
WHERE Calendar_Month = 1 
AND Calendar_Day = 2


;WITH NewYearsDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'January'
	AND Calendar_Day = 1
	AND Day_Name = 'Saturday'
)
UPDATE #Calendar
SET Holiday_Name = 'New Year''s Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN NewYearsDayOnSaturday nyd
	ON c.Calendar_Year = nyd.Calendar_Year
WHERE Calendar_Month = 1 
AND Calendar_Day = 3



--Memorial Day ----------------------------------------------------------------------------------------
--Last MonDay in May
;WITH LastMondayInMay AS
(
	SELECT	Calendar_Year
			,MAX(Month_Day_Name_Instance) AS LastMonday
	FROM #Calendar
	WHERE Month_Name = 'May'
	AND Day_Name = 'Monday'
	GROUP BY Calendar_Year
)
UPDATE #Calendar
SET Holiday_Name = 'Memorial Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
FROM #Calendar c
INNER JOIN LastMondayInMay lm
	ON c.Calendar_Year = lm.Calendar_Year
	AND c.Month_Day_Name_Instance = lm.LastMonday
WHERE c.Month_Name =  'May'
AND c.Day_Name = 'Monday'
 
 
--Labor Day -------------------------------------------------------------------------------------------
--First Monday in September
UPDATE #Calendar
SET Holiday_Name = 'Labor Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar
WHERE Month_Name = 'September'
AND Day_Name = 'Monday'
AND Month_Day_Name_Instance = 1


-- Valentine's Day ---------------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Valentine''s Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 2 AND Calendar_Day = 14


-- Saint Patrick's Day -----------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Saint Patrick''s Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 3 AND Calendar_Day = 17
GO

--Martin Luthor King Day ---------------------------------------------------------------------------------------
--Third MonDay in January starting in 1983
UPDATE #Calendar
SET Holiday_Name = 'Martin Luthor King Jr Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 1--January
 AND Day_Name = 'MonDay'
 AND Calendar_Year >= 1983--When Holiday was official
 AND Month_Day_Name_Instance = 3--Third X Day of current month.
GO

--President's Day ---------------------------------------------------------------------------------------
--Third MonDay in February.
UPDATE #Calendar
SET Holiday_Name = 'President''s Day'--select * from dim_date
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 2--February
 AND Day_Name = 'MonDay'
 AND Month_Day_Name_Instance = 3--Third occurance of a monDay in this month.
GO

--Mother's Day ---------------------------------------------------------------------------------------
--Second SunDay of May
UPDATE #Calendar
SET Holiday_Name = 'Mother''s Day'--select * from dim_date
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 5--May
 AND Day_Name = 'SunDay'
 AND Month_Day_Name_Instance = 2--Second occurance of a monDay in this month.
GO

--Father's Day ---------------------------------------------------------------------------------------
--Third SunDay of June
UPDATE #Calendar
SET Holiday_Name = 'Father''s Day'--select * from dim_date
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 6--June
 AND Day_Name = 'SunDay'
 AND Month_Day_Name_Instance = 3--Third occurance of a monDay in this month.
GO

--Halloween 10/31 ----------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Halloween'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 0	
WHERE Calendar_Month = 10 AND Calendar_Day = 31


--Victoria Day  ----------------------------------------------------------------------------------
-- second last Monday in May
;WITH SecondLastMondayInMay AS
(
	SELECT	Calendar_Year
			,(MAX(Month_Day_Name_Instance) - 1) AS SecondLastMonday
	FROM #Calendar
	WHERE Month_Name = 'May'
	AND Day_Name = 'Monday'
	GROUP BY Calendar_Year
)
UPDATE #Calendar
SET Holiday_Name = 'Victoria Day'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN SecondLastMondayInMay lm
	ON c.Calendar_Year = lm.Calendar_Year
	AND c.Month_Day_Name_Instance = lm.SecondLastMonday
WHERE c.Month_Name =  'May'
AND c.Day_Name = 'Monday'
 
 


--Remembrance Day -------------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Remembrance Day'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 11 
AND Calendar_Day = 11


;WITH RemembranceDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'November'
	AND Calendar_Day = 11
	AND Day_Name = 'Sunday'
)
UPDATE #Calendar
SET Holiday_Name = 'Remembrance Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN RemembranceDayOnSunday rd
	ON c.Calendar_Year = rd.Calendar_Year
WHERE Calendar_Month = 11 
AND Calendar_Day = 12


;WITH RemembranceDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'November'
	AND Calendar_Day = 11
	AND Day_Name = 'Saturday'
)
UPDATE #Calendar
SET Holiday_Name = 'Remembrance Day (in Lieu)'
	,Is_Holiday_USA = 1
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN RemembranceDayOnSaturday rd
	ON c.Calendar_Year = rd.Calendar_Year
WHERE Calendar_Month = 11 
AND Calendar_Day = 13



--Canada Day -------------------------------------------------------------------------------------------
UPDATE #Calendar
SET Holiday_Name = 'Canada Day'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 7 
AND Calendar_Day = 1

;WITH CanadaDayOnSunday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'July'
	AND Calendar_Day = 1
	AND Day_Name = 'Sunday'
)
UPDATE #Calendar
SET Holiday_Name = 'Canada Day (in Lieu)'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN CanadaDayOnSunday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 7 
AND Calendar_Day = 2



;WITH CanadaDayOnSaturday AS
(
	SELECT	Calendar_Year
	FROM #Calendar
	WHERE Month_Name = 'July'
	AND Calendar_Day = 1
	AND Day_Name = 'Saturday'
)
UPDATE #Calendar
SET Holiday_Name = 'Canada Day (in Lieu)'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
FROM #Calendar c
INNER JOIN CanadaDayOnSaturday cd
	ON c.Calendar_Year = cd.Calendar_Year
WHERE Calendar_Month = 7 
AND Calendar_Day = 3



----Easter Sunday -------------------------------------------------------------------------------------------
--;WITH Years AS
--(
--	SELECT DISTINCT Calendar_Year
--	FROM #Calendar
--)
--UPDATE c
--SET Holiday_Name = 'Easter Sunday'
--	,Is_Holiday_USA = 1
--	,Is_Holiday_CANADA = 1
--FROM #Calendar c
--INNER JOIN Years y 
--	ON c.Calendar_Date= dbo.udf_EasterSundayByYear(y.Calendar_Year)


----Good Friday -------------------------------------------------------------------------------------------
--;WITH Years AS
--(
--	SELECT DISTINCT Calendar_Year
--	FROM #Calendar
--)
--UPDATE c
--SET Holiday_Name = 'Good Friday'
--	,Is_Holiday_USA = 1
--	,Is_Holiday_CANADA = 1
--FROM #Calendar c
--INNER JOIN Years y 
--	ON c.Calendar_Date= DATEADD(dd, -2, dbo.udf_EasterSundayByYear(y.Calendar_Year))

--CREATE NONCLUSTERED INDEX [IX_Is_Holiday_USA] ON #Calendar (Is_Holiday_USA)
--CREATE NONCLUSTERED INDEX [IX_Is_Holiday_CANADA] ON #Calendar (Is_Holiday_CANADA)


--Civic Holiday ---------------------------------------------------------------------------------------
--First Monday of August
UPDATE #Calendar
SET Holiday_Name = 'Civic Holiday'
	,Is_Holiday_USA = 0
	,Is_Holiday_CANADA = 1	
WHERE Calendar_Month = 8--June
 AND Day_Name = 'Monday'
 AND Month_Day_Name_Instance = 1--First occurance of a monDay in this month.
GO


------------------------------------------------------------------------------------------------------------------------
----	Main Query: Final Display/Output
------------------------------------------------------------------------------------------------------------------------

--SELECT
--	URD.*
--FROM
--	#Calendar URD
--ORDER BY
--	URD.Calendar_Date