Create Master Calendar Table

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Various/ChillyDBA

Date: 21 Jun 2012

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

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