Embedded Files
Applicability:
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Credits:
Author: Various/ChillyDBA
Date: 21 Jun 2012
Description
Description
Many times over the years, I have needed to create Calendar tables for Data Warehouse implementations.
One highly useful/desirable/essential feature of these has been the precacluation of various date attributes (eg Quarter, DayofWeek, Holidays). This virtually eliminates the need for complex TSQL/MDX in ETL or Aggregation code and can improve performance.
The requirements for each implementation have generally been subtly different, so I have amalgamated these into a superset in one giant Calendar table creation script. Some of the TSQL could probably be optimised, but it didn't really seem to be necessary as the creation of this table is usually a one-off event (or at least restricted to once every 10 years or so)
One point to make is that there is extensive use of CTEs, especially in the latter stages of the code, which precludes the use against SQL 2000.
Also, the code uses the udf_EasterSundaybyYear function defined elsewhere on this site.
A quick summary of the columns is as follows:
The code below has been split into sections for greater readability, but is contained in a single attached file.
Code
Code
Calendar Table Creation and Initial Population:
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 ('dbo.Calendar') IS NOT NULL
BEGIN
DROP TABLE dbo.Calendar
END
----------------------------------------------------------------------------------------------------------------------
-- Permanent Table: Create Date Xref Table
----------------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.Calendar
(
Calendar_Date DATETIME 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 dbo.Calendar
(
Calendar_Date
)
SELECT
CTE.Calendar_Date
FROM
cte_date_base_table CTE
OPTION
(MAXRECURSION 0)
Calendar Table Update Pass I - Simple Attributes
----------------------------------------------------------------------------------------------------------------------
-- Table Update I: Populate Additional Date Xref Table Fields (Pass I)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.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 dbo.Calendar ALTER COLUMN Calendar_Year INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Calendar_Month INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Calendar_Day INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Calendar_Quarter INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN First_Day_in_Week DATETIME NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN First_Day_in_Month DATETIME NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Day_of_Week INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Week_of_Year INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Day_of_Year INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Is_WeekDay INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Day_Name VARCHAR (10) NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Month_Name VARCHAR (10) NOT NULL
CREATE NONCLUSTERED INDEX [IX_Calendar_Year] ON dbo.Calendar (Calendar_Year)
CREATE NONCLUSTERED INDEX [IX_Calendar_Month] ON dbo.Calendar (Calendar_Month)
CREATE NONCLUSTERED INDEX [IX_Calendar_Quarter] ON dbo.Calendar (Calendar_Quarter)
CREATE NONCLUSTERED INDEX [IX_First_Day_in_Week] ON dbo.Calendar (First_Day_in_Week)
CREATE NONCLUSTERED INDEX [IX_Day_of_Week] ON dbo.Calendar (Day_of_Week)
CREATE NONCLUSTERED INDEX [IX_Is_WeekDay] ON dbo.Calendar (Is_WeekDay)
Calendar Table Update Pass II - Simple Relative Attributes:
----------------------------------------------------------------------------------------------------------------------
-- Table Update II: Populate Additional Date Xref Table Fields (Pass II)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.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
dbo.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 dbo.Calendar ALTER COLUMN Last_Day_in_Week DATETIME NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Last_Day_in_Month DATETIME NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Last_Day_in_Week DATETIME NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Week_of_Month INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Week_of_Quarter INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Is_Leap_Year INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Year_Week VARCHAR (6) NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Year_Month VARCHAR (6) NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Year_Quarter VARCHAR (6) NOT NULL
CREATE NONCLUSTERED INDEX [IX_Last_Day_in_Week] ON dbo.Calendar (Last_Day_in_Week)
CREATE NONCLUSTERED INDEX [IX_Year_Month] ON dbo.Calendar (Year_Month)
CREATE NONCLUSTERED INDEX [IX_Year_Quarter] ON dbo.Calendar (Year_Quarter)
Calendar Table Update Pass III - Complex Relative Attributes Part 1:
----------------------------------------------------------------------------------------------------------------------
-- Table Update III: Populate Additional Date Xref Table Fields (Pass III)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.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
dbo.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
dbo.Calendar X
GROUP BY
X.Year_Quarter
) B
WHERE
A.Year_Month = dbo.Calendar.Year_Month
AND B.Subquery_Year_Quarter = Year_Quarter
ALTER TABLE dbo.Calendar ALTER COLUMN Is_Last_Day_in_Month INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Is_Last_Day_in_Quarter INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Days_in_Month INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN WeekDays_in_Month INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Days_in_Quarter INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Quarter_Days_Remaining INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN WeekDays_in_Quarter INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Year_Days_Remaining INT NOT NULL
Calendar Table Update Pass IV and V - Complex Relative Attributes Part 2:
----------------------------------------------------------------------------------------------------------------------
-- Table Update IV: Populate Additional Date Xref Table Fields (Pass IV)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.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
dbo.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
dbo.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
dbo.Calendar X
LEFT JOIN dbo.Calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
AND Y.year_month = X.year_month
LEFT JOIN dbo.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 dbo.Calendar ALTER COLUMN Month_Weekdays_Remaining INT NOT NULL
Calendar Table Update Pass VI - Complex Relative Attributes Part 3:
----------------------------------------------------------------------------------------------------------------------
-- Table Update VI: Populate Additional Date Xref Table Fields (Pass VI)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.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
dbo.Calendar X
) A
WHERE
A.Calendar_Date = dbo.Calendar.Calendar_Date
ALTER TABLE dbo.Calendar ALTER COLUMN Is_Week_in_Same_Month INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Month_Days_Remaining INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Month_WeekDays_Completed INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Quarter_Days_Completed INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Quarter_Weekdays_Completed INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Month_Day_Name_Instance INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Quarter_Day_Name_Instance INT NOT NULL
ALTER TABLE dbo.Calendar ALTER COLUMN Year_Day_Name_Instance INT NOT NULL
Calendar Table Update Pass VII - Holiday Calculations:
----------------------------------------------------------------------------------------------------------------------
-- 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 Junne
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 dbo.Calendar (Is_Holiday_USA)
CREATE NONCLUSTERED INDEX [IX_Is_Holiday_CANADA] ON dbo.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
dbo.Calendar URD
ORDER BY
URD.Calendar_Date
Page updated
Google Sites
Report abuse