Date and Time - 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:
Calendar_Date Date - no time component.
Calendar_Year 4-digit integer year.
Calendar_Month 2 digit integer month number.
Calendar_Day 2 digit integer day.
Calendar_Day_Suffix 2 character calendar day suffix  -  'st', 'nd', 'rd' or 'th'.
Calendar_Quarter 1 digit integer quarter.  May be prefixed with 'Q' on retrieval.
Calendar_Quarter_Name Full textual quarter name - 'First', 'Second', 'Third' or 'Fourth'.
First_Day_in_Week Date of the first day of the week in which this day falls.
Last_Day_in_Week Date of the last day of the week in which this day falls.
Is_Week_in_Same_Month Do the days of the week all fall in the same calendar month?  1 = Yes, 0 = No. 
First_Day_in_Month Date of the first day of the month in which this day falls.
Last_Day_in_Month Date of the last day of the month in which this day falls.
 Is_Last_Day_In_Month Is this day the last day in the month?  1 = Yes, 0 = No 
 First_Day_in_Quarter Date of the first day of the quarter in which this day falls.
 Last_Day_in_Quarter Date of the last day of the quarter in which this day falls.
 Is_Last_Day_In_Quarter Is this day the last day in the quarter?  1 = Yes, 0 = No 
Day_of_Week 1 digit integer representing the day number in the week.  Takes into account the server settings for first day of week. 
Day_of_Year 3 digit integer representing the day number in the year.
Week_of_Month 1 digit integer representing the week number in the month in which this day falls. 
Week of Quarter 1 digit integer representing the week number in the quarter in which this day falls. 
Week_of_Year 2 digit integer representing the week number in the year in which this day falls. 
Days_in_Month 2 digit integer representing the number of days in the month in which this day falls. 
Month_Days_Remaining 2 digit integer representing the number of days remaining in the month in which this day falls.
WeekDays_in_Month 2 digit integer representing the number of weekdays in the month in which this day falls.
Month_WeekDays_Remaining 2 digit integer representing the number of weekdays remaining in the month in which this day falls.
Month_WeekDays_Completed 2 digit integer representing the number of weekdays that have passed in the month in which this day falls.
Days_in_Quarter 3 digit integer representing the number of days in the quarter in which this day falls.  
Quarter_Days_Remaining 3 digit integer representing the number of days remaining in the quarter in which this day falls.
Quarter_Days_Completed 3 digit integer representing the number of days completed in the quarter in which this day falls.
WeekDays_in_Quarter 3 digit integer representing the number of weekdays in the quarter in which this day falls.  
Quarter_WeekDays_Remaining 3 digit integer representing the number of weekdays remaining in the quarter in which this day falls.
Quarter_WeekDays_Completed 3 digit integer representing the number of weekdays completed in the quarter in which this day falls.
Year_Days_Remaining 3 digit integer representing the number of days remaining in the year in which this day falls. 
Is_WeekDay Is the current day a weekday?  1 = Yes, 0 = No .
Is_Leap_Year Is the year in which the current day falls a leap year? 1 = Yes, 0 = No .
Day_Name Textual name of the current day.
Month_Day_Name_Instance 1 digit integer representing the number of times this day name has repeated in the month so far  i.e. 3rd Monday of the month.
Quarter_Day_Name_Instance 2 digit integer representing the number of times this day name has repeated in the quarter so far  i.e. 7th Monday of Q2.
Year_Day_Name_Instance 2 digit integer representing the number of times this day name has repeated in the year so far  i.e. 13th Monday of the year.
Month_Name Textual name of the month in which the current day falls.
Year_Week 6 digit numeric string representing the week number of the year in which the current day falls e.g. 201201 or 201252
Year_Month 6 digit numeric string representing the month number of the year in which the current day falls e.g. 201201 or 201212
Year_Quarter 6 character alphanumeric string representing the quarter of the year in which the current day falls e.g. 2012Q1 or 2012Q2
Is_Holiday_USA 1 digit integer representing whether the current day is a public/civic holiday in the USA.  1 = Yes, 0 = No
Is_Holiday_CANADA 1 digit integer representing whether the current day is a public/civic holiday in CANADA. 1 = Yes, 0 = No
Holiday_Name textual name of the holiday. 
  
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
 

ċ
All- purpose calendar table.sql
(27k)
Andy Hughes,
Jun 25, 2012, 8:19 AM
Comments