Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
--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)
USE WideWorldImporters
GO
--Create a copy of the Calendar Table here
--Populate Calendar Table
EXEC PopulateCalendarTable
@vDate_Start = '20100101',
@vDate_End = '20391231'
--Verify rows in Calendar table
select top(10) * from Calendar
--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
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
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
--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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment