/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Created by: Alex Juliano
Purpose: Create ‘Dates’ as centralized table for other processes to populate common date fields off of cal_date
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Declare Variables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
DECLARE @StartDate AS DATE = ‘20100101’
DECLARE @NumberOfYears AS INT = 10
DECLARE @CutoffDate AS DATE = Dateadd(YEAR, @NumberOfYears, @StartDate)
DECLARE @minyear AS INT = Datepart(YEAR, @StartDate)
DECLARE @MAXyear AS INT = Datepart(YEAR, @CutoffDate)
DECLARE @m AS INT = @minyear
DECLARE @currentdate AS DATE = @StartDate
DECLARE @YearStart AS DATE = Dateadd(YEAR, Datediff(YEAR, 0, @currentdate), 0)
DECLARE @YearEnd AS DATE = Dateadd(DAY, –1, Dateadd(YEAR, Datediff(YEAR, 0, @currentdate) + 1, 0))
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Create Main Table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/*Check to see if table exists; if it does, drop it And rebuild according to the specified criteria*/
IF Object_id(‘Dates’) IS NOT NULL
DROP TABLE Dates
CREATE TABLE Dates
(
[cal_date] DATE PRIMARY KEY,
[reg_date] AS CONVERT(DATE, [cal_date], 0),
[business_day] INT DEFAULT 0,
[day] AS Datepart(DAY, [cal_date]),
[day_of_week] AS Datepart(WEEKDAY, [cal_date]),
[iso_week] AS Datepart(ISO_WEEK, [cal_date]),
[week] AS Datepart(WEEK, [cal_date]),
[month] AS Datepart(MONTH, [cal_date]),
[quarter] AS Datepart(QUARTER, [cal_date]),
[year] AS Datepart(YEAR, [cal_date]),
[yyyy-mm] AS CONVERT(CHAR(7), [cal_date], 121),
first_of_month AS CONVERT(DATE, Dateadd(MONTH, Datediff(MONTH, 0, [cal_date]), 0)),
last_of_month AS CONVERT(DATE, EOMONTH([cal_date], 0)),
first_of_year AS CONVERT(DATE, Dateadd(YEAR, Datediff(YEAR, 0, [cal_date]), 0)),
end_of_year AS CONVERT(DATE, Dateadd(DAY, –1, Dateadd(YEAR, Datediff(YEAR, 0, [cal_date]) + 1, 0))),
[month_name] AS Datename(MONTH, [cal_date]),
[day_name] AS Datename(WEEKDAY, [cal_date]),
Style112 AS CONVERT(CHAR(8), [cal_date], 112),
Style101 AS CONVERT(CHAR(10), [cal_date], 101)
);
/*Append all cal_dates between specified years range into ‘Dates’ table*/
INSERT Dates
([cal_date])
SELECT d
FROM (SELECT d = Dateadd(DAY, rn – 1, @StartDate)
FROM (SELECT TOP (Datediff(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER()
OVER (
ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]) AS x) AS y;
/*Update business_day field to NULL where the day of week is Sat or Sun*/
UPDATE Dates
SET business_day = NULL
WHERE [day_of_week] IN( 1, 7 )
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Calcuate Holidays By Year
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- Calculate business holidays closed for a given year range
-- This program uses the ‘Dates’ table to compute given holidays.
DECLARE @year INT,
@byear INT,
@eyear INT,
@day DATETIME,
@eve DATETIME,
@dow1 INT,
@dow2 INT
-- used for Easter calculation
DECLARE @month INT,
@Intday INT,
@tmpdate CHAR(10),
@g INT,
@c INT,
@h INT,
@i INT,
@j INT,
@l INT
-- Enter year range you wish to SET
SET @byear = Year(@StartDate)
SET @eyear = @byear + @NumberOfYears
-- UPDATE Dates SET business_day = NULL
-- WHERE year = @year AND month = 1 AND day >= 1 AND [day_of_week] NOT IN(1, 7)
SET @year = @byear
WHILE @year <= @eyear
BEGIN
-- Calculate New Year’s Day
-- first business day of the new year
UPDATE Dates
SET business_day = NULL
WHERE cal_date = (SELECT TOP 1 cal_date
FROM dates
WHERE [year] = @year
AND [month] = 1
AND [day] >= 1
AND [day_of_week] NOT IN( 1, 7 )
ORDER BY cal_date)
-- Calculate Martin Luther King Holiday
-- 3rd Monday of January
UPDATE Dates
SET business_day = NULL
WHERE [year] = @year
AND [month] = 1
AND [day_of_week] = 2
AND [day] BETWEEN 15 AND 21
-- Calculate President’s Day Holiday
-- 3rd Monday of February
UPDATE Dates
SET business_day = NULL
WHERE [year] = @year
AND [month] = 2
AND [day_of_week] = 2
AND [day] BETWEEN 15 AND 21
-- Calculate Good Friday Holiday
-- compute Easter with existing known algorithm, THEN subtract to Friday
SET @g = @year % 19
SET @c = @year / 100
SET @h = ( ( @c – ( @c / 4 ) – ( ( 8 * @c + 13 ) / 25 ) + ( 19 * @g ) + 15 ) % 30 )
SET @i = @h – ( ( @h / 28 ) * ( 1 – ( @h / 28 ) * ( 29 / ( @h + 1 ) ) * ( ( 21 – @g ) / 11 ) ) )
SET @j = ( ( @year + ( @year / 4 ) + @i + 2 – @c + ( @c / 4 ) ) % 7 )
SET @l = @i – @j
SET @month = 3 + ( ( @l + 40 ) / 44 )
SET @intday = @l + 28 – ( 31 * ( @month / 4 ) )
SET @tmpdate = Cast(@month AS VARCHAR(2)) + ‘/’
+ Cast(@intday AS VARCHAR(2)) + ‘/’
+ Cast(@year AS VARCHAR(4))
SET @day = Cast(@tmpdate as DATETIME) – 2
UPDATE Dates
SET business_day = NULL
WHERE cal_date = @day
-- Calculate Memorial Day Holiday
-- last Monday of May
UPDATE Dates
SET business_day = NULL
WHERE cal_date = (SELECT Max(cal_date)
FROM dates
WHERE [year] = @year
AND [month] = 5
AND [day_of_week] = 2)
-- Calculate 4th of July Holiday
-- if Sat THEN Fri, if Sun THEN Mon
SET @day = (SELECT cal_date
FROM dates
WHERE [year] = @year
AND [month] = 7
AND [day] = 4)
SET @dow1 = (SELECT [day_of_week]
FROM dates
WHERE cal_date = @day)
SET @day = ( CASE
WHEN @dow1 = 1 THEN @day + 1 -- Sun
WHEN @dow1 = 7 THEN @day – 1 -- Sat
ELSE @day
END )
UPDATE Dates
SET business_day = NULL
WHERE cal_date = @day
-- Calculate Labor Day Holiday
-- 1st Monday of September
UPDATE Dates
SET business_day = NULL
WHERE [year] = @year
AND [month] = 9
AND [day_of_week] = 2
AND [day] BETWEEN 1 AND 7
-- Calculate Thanksgiving Day Holiday
-- 4th Thursday of November
SET @day = (SELECT cal_date
FROM dates
WHERE [year] = @year
AND [month] = 11
AND [day_of_week] = 5
AND [day] BETWEEN 22 AND 28)
UPDATE Dates
SET business_day = NULL
WHERE cal_date = @day
-- Calculate Christmas Holiday
-- Christmas Day (days off not on weekend)
SET @eve = (SELECT cal_date
FROM dates
WHERE [year] = @year
AND [month] = 12
AND [day] = 24)
SET @day = @eve + 1
SET @dow1 = (SELECT [day_of_week]
FROM dates
WHERE cal_date = @eve)
SET @dow2 = (SELECT [day_of_week]
FROM dates
WHERE cal_date = @day)
SET @eve = ( CASE
WHEN @dow1 = 1
AND @dow2 = 2 THEN @eve + 1 -- Sun Mon
WHEN @dow1 = 7
AND @dow2 = 1 THEN @eve – 1 -- Sat Sun
ELSE @eve
END )
SET @day = ( CASE
WHEN @dow1 = 1
AND @dow2 = 2 THEN @day -- Sun Mon
WHEN @dow1 = 6
AND @dow2 = 7 THEN @day + 2 -- Fri Sat
WHEN @dow1 = 7
AND @dow2 = 1 THEN @day + 1 -- Sat Sun
ELSE @day
END )
UPDATE Dates
SET business_day = NULL
WHERE cal_date = @day
SET @year = @year + 1
END;
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Calcuate Business Days By Year
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/*Loop through cal_date to populate BDs by Year*/
UPDATE [dbo].[Dates]
SET [business_day] = date_sub.BizDay
FROM (SELECT [cal_date],
ROW_NUMBER()
OVER(
PARTITION BY d.[Year]
ORDER BY [cal_date] ASC) AS BizDay
FROM [dbo].[Dates] AS d
WHERE business_day = 0) AS date_sub
INNER JOIN [dbo].[Dates] AS d
ON d.cal_date = date_sub.cal_date;