I do solemnly swear that if this information is found useful and actually deployed into production, to make a contribution to the Jim Horn Good Guy Retirement Fund, care of Experts Exchange. That, or click the Yes button at the end of this article to state that this article was helpful.Now let's get this show started.
/*
Build a table of days
08-22-13 Jim Horn Original
02-22-15 Jim Horn Multiple fixes to holidays, added continous_ columns to set up second article
09-03-15 Jim Horn Added more events, changed tab to three spaces so it renders better in EE's article designer
09-17-15 Jim Horn Moved the start date back to 2000-01-01 to facilitate queries on database AdventureWorksDW2012.
*/
SET NOCOUNT ON
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name='calendar')
DROP DATABASE calendar
GO
CREATE DATABASE calendar
GO
USE calendar
GO
-- 1=Sunday to 7=Saturday
SET DATEFIRST 7
GO
IF EXISTS(SELECT * FROM sys.tables WHERE name='days')
DROP TABLE days
GO
CREATE TABLE days (
PKDate date NOT NULL PRIMARY KEY CLUSTERED,
-- Years
calendar_year smallint,
-- Quarters
calendar_quarter tinyint,
calendar_quarter_desc varchar(10),
-- Months
calendar_month tinyint,
calendar_month_name_long varchar(30),
calendar_month_name_short varchar(10),
-- Weeks
calendar_week_in_year tinyint,
calendar_week_in_month tinyint,
-- Days
calendar_day_in_year smallint,
calendar_day_in_week tinyint, -- The first of the month
calendar_day_in_month tinyint,
mdy_name_long varchar(30),
mdy_name_long_with_suffix varchar(30),
day_name_long varchar(10),
day_name_short varchar(10),
-- Continuous Y/M/D, starts with the first day = 1 and keeps going. Used for various dateadd functions.
continuous_year tinyint,
continuous_quarter smallint,
continuous_month smallint,
continuous_week smallint,
continuous_day int,
-- Custom
description varchar(100),
is_weekend tinyint, -- Tinyint and not bit so you can add the 1's.
is_holiday tinyint, -- Tinyint and not bit so you can add the 1's.
is_workday tinyint, -- Tinyint and not bit so you can add the 1's.
is_event tinyint) -- Used to indicate any special event days.
GO
-- Create the table, with dates ranging from 2010 to 2020. Change to suit your needs.
Declare @dt_start date = '2000-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end)
WHILE @i <= @total_days
begin
INSERT INTO days (PKDate)
SELECT CAST(DATEADD(d, @i, @dt_start) as DATE)
SET @i = @i + 1
end
-- These values can be generated with single SQL Server functions
UPDATE days
SET
calendar_year = YEAR(PKDate),
calendar_quarter = DATEPART(q, PKDate),
calendar_month = DATEPART(m, PKDate),
calendar_week_in_year = DATEPART(WK, PKDate),
calendar_day_in_year = DATEPART(dy, PKDate),
calendar_day_in_week = DATEPART(Weekday, PKDate),
calendar_day_in_month = DATEPART(d, PKDate),
day_name_long = datename(weekday, PKDate)
-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET
is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END,
calendar_quarter_desc = 'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)),
calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', ' + CAST(calendar_year as CHAR(4)),
day_name_short = LEFT(datename(weekday, PKDate),3)
-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET
is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END,
calendar_quarter_desc = 'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)),
calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', ' + CAST(calendar_year as CHAR(4)),
day_name_short = LEFT(datename(weekday, PKDate),3)
UPDATE days
SET calendar_week_in_month =
CASE
WHEN calendar_day_in_month BETWEEN 1 AND 7 THEN 1
WHEN calendar_day_in_month BETWEEN 8 AND 14 THEN 2
WHEN calendar_day_in_month BETWEEN 15 AND 21 THEN 3
WHEN calendar_day_in_month BETWEEN 22 AND 28 THEN 4
ELSE 5
END
-- Month name: The first three letters of the month.
UPDATE days
SET calendar_month_name_short = LEFT(DATENAME(month, PKDate),3)
/*
-- If the above doesn't work and you want to spell it out, then use this..
UPDATE days
SET calendar_month_name_short = CASE calendar_month
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'June'
WHEN 7 THEN 'July' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END
*/
-- Fancy schmancy full date column, which adds the suffix st, nd, rd, or th to the day.
UPDATE days
SET mdy_name_long_with_suffix = CASE RIGHT(CAST(calendar_day_in_month as varchar(2)), 1)
WHEN '1' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'st, ' + CAST(calendar_year as CHAR(4))
WHEN '2' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'nd, ' + CAST(calendar_year as CHAR(4))
WHEN '3' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'rd, ' + CAST(calendar_year as CHAR(4))
ELSE DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'th, ' + CAST(calendar_year as CHAR(4)) END
UPDATE days
SET
continuous_year = DATEDIFF(year, @dt_start, PKDate) + 1,
continuous_quarter = DATEDIFF(QUARTER , @dt_start, PKDate) + 1,
continuous_month = DATEDIFF(month, @dt_start, PKDate) + 1,
continuous_week = DATEDIFF(week, @dt_start, PKDATE) + 1,
continuous_day = DATEDIFF(day, @dt_start, PKDATE) + 1
-- HOLIDAYS WHERE THE DAY IS ALWAYS THE SAME
-- Fixed Holidays, Mondays through Fridays.
-- (see http://www.cute-calendar.com/category/federal-holidays-in-the-united-states.html)
UPDATE days
SET is_holiday = 1, description = 'American Martin Luther King Jr. Birthday' -- third Monday in January
WHERE calendar_month = 1 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Presidents Day ' -- third Monday in February
WHERE calendar_month = 2 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Labor Day' -- first Monday of September
WHERE calendar_month = 9 AND calendar_week_in_month = 1 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Columbus Day' -- second Monday in October
WHERE calendar_month = 10 AND calendar_week_in_month = 2 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 5)
UPDATE days
SET is_holiday = 1, description = 'Day after American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 6)
-- Fixed Holidays by specific date, such as July 4th, but if on a weekend would force either the previous Friday or following Monday to be the holiday.
-- HOLIDAYS WHERE THE DAYS MAY CHANGE
UPDATE days
SET is_holiday = 1, description = 'New Year''s Day' -- - January 1st
WHERE (calendar_month = 1 AND calendar_day_in_month = 1) -- 1/1 when 1/1 is Monday through Friday
UPDATE days
SET is_holiday = 1, description = 'New Year''s Day observed' -- - January 1st
WHERE
(calendar_month = 12 AND calendar_day_in_month = 31 AND calendar_day_in_week = 6) OR -- Friday 12/31 when 1/1 is Saturday
(calendar_month = 1 AND calendar_day_in_month = 2 AND calendar_day_in_week = 2) -- Monday 1/2 when 1/1 is Sunday
-- American Independence Day - July 4th – Or if 7/4 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days
SET is_holiday = 1, description = 'American Independence Day'
WHERE (calendar_month = 7 AND calendar_day_in_month = 4)
UPDATE days
SET is_holiday = 1, description = 'American Independence Day observed'
WHERE
(calendar_month = 7 AND calendar_day_in_month = 3 AND calendar_day_in_week = 6) OR
(calendar_month = 7 AND calendar_day_in_month = 5 AND calendar_day_in_week = 2)
-- American Veterans Day - November 11th – Or if 11/11 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days
SET is_holiday = 1, description = 'American Veterans Day'
WHERE (calendar_month = 11 AND calendar_day_in_month = 11)
UPDATE days
SET is_holiday = 1, description = 'American Veterans Day observed'
WHERE (calendar_month = 11 AND calendar_day_in_month = 10 AND calendar_day_in_week = 6) OR
(calendar_month = 11 AND calendar_day_in_month = 12 AND calendar_day_in_week = 2)
-- Christmas - December 24th and 25th - Or if one or the other falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days
SET is_holiday = 1, description = 'Christmas'
WHERE (calendar_month = 12 AND calendar_day_in_month = 25)
UPDATE days
SET is_holiday = 1, description = 'Christmas observed'
WHERE (calendar_month = 12 AND calendar_day_in_month = 24 AND calendar_day_in_week = 6) OR
(calendar_month = 12 AND calendar_day_in_month = 26 AND calendar_day_in_week = 2)
-- Memorial Day
UPDATE days
SET is_holiday = 1, description = 'Memorial Day' -- last Monday in May --select *
FROM days
JOIN (
SELECT max(calendar_week_in_month) WkInMonth , year(pkdate) as Yr
FROM days d
WHERE d.calendar_month = 5 and (calendar_day_in_week = 2)
GROUP BY year(PKDate)) D on D.WkInMonth = days.calendar_week_in_month and D.Yr = year(pkdate) and days.calendar_month = 5 AND calendar_day_in_week = 2 -- Monday
-- Set the non-holiday days
UPDATE days SET is_holiday = 0 WHERE is_holiday IS NULL
UPDATE days SET is_workday = CASE WHEN is_weekend = 0 AND is_holiday = 0 THEN 1 ELSE 0 END
-- Client-defined special events
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp (PKDate date, description varchar(100))
INSERT INTO #tmp (PKDate, description)
VALUES
('2015-01-02', 'Frank Caliendo'),
('2015-01-05', 'Rock of the 80''s'),
('2015-01-15', 'Smokey Robinson Pre-Show Wine & Smoked Cheese Tasting'),
('2015-01-16', 'Smokey Robinson'),
('2015-01-22', 'Rewind Fest 2015: Psychedelic Furs and More'),
('2015-02-04', 'ZZ Top with Special Guest Blackberry Smoke'),
('2015-02-07', 'Block Party With Kool & The Gang And More'),
('2015-02-13', 'Thunder Vibes Reggae Festival'),
('2015-02-14', 'Michael McDonald and Boz Scaggs'),
('2015-02-16', 'Marco Antonio Solis Y Camilia - La Experiencia Tour'),
('2015-02-26', 'Gladiator Challenge: Collision Course'),
('2015-02-27', 'Rick Springfield'),
('2015-03-04', 'The Thunder Down Under Australian Nudie Revue'),
('2015-03-05', 'The Cosplay Five Sings The Hits'),
('2015-03-10', 'Strawberry Music Fest - Amy''s Orchid Late Nite Lounge'),
('2015-03-27', 'Kenny G: The Rock Opera'),
('2015-03-28', 'Purple Ones - Tribute to the Music of Prince'),
('2015-03-31', 'Steel Slinky - Party Band'),
('2015-04-05', 'Chains Required - Horn driven R&B'),
('2015-04-17', 'Cheezy Poofs = A Dash of alt-rock with soul and funk'),
('2015-04-23', 'Rewind Fest 2015: Psychedelic Furs and More'),
('2015-04-24', 'Shaq''s All-Star Comedy Jam'),
('2015-05-15', 'Peppermint Patty and Mustafa''s All Star Jamacian Steel Drum Band'),
('2015-05-21', 'IFC Caged Combat'),
('2015-06-15', 'The Fabulous Jewish Magician Signumd J. Goldstein'),
('2015-06-30', 'Pearl City Marathon'),
('2015-07-20', 'Tough Mudder'),
('2015-09-05', 'Huey Lewis and The News' ),
('2015-09-18', 'Sarah Colonna and feature act Jeff Bodart'),
('2015-09-19', 'Sarah Colonna and feature act Jeff Bodart'),
('2015-09-12', 'Duc Huy: 50 Years of Love and Music'),
('2015-09-25', 'ABBACADABRA–The Ultimate ABBA Tribute'),
('2015-09-26', 'Last Comic Standing Live Tour'),
('2015-09-09', 'America''s Got Talent Live: The All-Stars Tour!'),
('2015-10-29', 'Flashdance – The Musical'),
('2015-10-10', 'Kenny Rogers' ),
('2015-10-31', 'Sinners & Saints Halloween Party'),
('2015-11-20', 'Donny & Marie Celebrating the Holidays'),
('2015-11-21', 'Donny & Marie Celebrating the Holidays')
-- Set the days with events
UPDATE d
SET d.is_event = 1, d.Description = t.description
FROM #tmp t
JOIN days d ON t.PKDate = d.PKDate
-- Set the days without events
UPDATE days SET is_event = 0 WHERE is_event IS NULL
Declare @dtStart as date = '2013-01-01', @dtEnd as date = '2013-01-20'
SELECT SUM(is_workday)
FROM days
WHERE PKDate BETWEEN @dtStart and @dtEnd
SELECT PKDate
FROM days
WHERE PKDate BETWEEN @dtStart and @dtEnd AND is_workday = 1
Declare @dt date = CAST(GETDATE() as date) , @dtWeekBegin as date, @dtWeekEnd as date
SELECT MIN(PKDate), MAX(PKDate)
FROM days
WHERE continuous_week = (SELECT continuous_week - 1 FROM days WHERE PKDate = @dt)
Declare @dt date = CAST(GETDATE() as date) , @dtWeekBegin as date, @dtWeekEnd as date
SELECT MIN(PKDate), MAX(PKDate)
FROM days
WHERE continuous_week = (SELECT continuous_week FROM days WHERE PKDate = @dt)
;
WITH
low AS (SELECT calendar_year, calendar_week_in_year, Min(PKDate) as min_date FROM days GROUP BY calendar_year, calendar_week_in_year),
high AS (SELECT calendar_year, calendar_week_in_year, Max(PKDate) as max_date FROM days GROUP BY calendar_year, calendar_week_in_year),
low_long_name AS (SELECT days.calendar_year, days.calendar_week_in_year, days.mdy_name_long_with_suffix FROM days JOIN low ON low.min_date = days.PKDate),
high_long_name AS (SELECT days.calendar_year, days.calendar_week_in_year, days.mdy_name_long_with_suffix FROM days JOIN high ON high.max_date = days.PKDate)
select distinct days.calendar_year, days.calendar_week_in_year, CAST(low_long_name.mdy_name_long_with_suffix as varchar(20)) + ' - ' + CAST(high_long_name.mdy_name_long_with_suffix as varchar(20)) as week_day_range -- low.mdy_name_long_with_suffix + ' - ' + high.mdy_name_long_with_suffix
FROM days
JOIN low_long_name ON days.calendar_year = low_long_name.calendar_year AND days.calendar_week_in_year = low_long_name.calendar_week_in_year
JOIN high_long_name ON days.calendar_year = high_long_name.calendar_year AND days.calendar_week_in_year = high_long_name.calendar_week_in_year
ORDER BY calendar_year, calendar_week_in_year
Thank you for reading my article, feel free to leave me some feedback regarding the content or to recommend future work.
/*
Build a table of days
08-22-13 Jim Horn Original
02-22-15 Jim Horn Multiple fixes to holidays, added continous_ columns to set up second article
09-03-15 Jim Horn Added more events, changed tab to three spaces so it renders better in EE's article designer
09-17-15 Jim Horn Moved the start date back to 2000-01-01 to facilitate queries on database AdventureWorksDW2012.
*/
SET NOCOUNT ON
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name='calendar')
DROP DATABASE calendar
GO
CREATE DATABASE calendar
GO
USE calendar
GO
-- 1=Sunday to 7=Saturday
SET DATEFIRST 7
GO
IF EXISTS(SELECT * FROM sys.tables WHERE name='days')
DROP TABLE days
GO
CREATE TABLE days (
PKDate date NOT NULL PRIMARY KEY CLUSTERED,
-- Years
calendar_year smallint,
-- Quarters
calendar_quarter tinyint,
calendar_quarter_desc varchar(10),
-- Months
calendar_month tinyint,
calendar_month_name_long varchar(30),
calendar_month_name_short varchar(10),
-- Weeks
calendar_week_in_year tinyint,
calendar_week_in_month tinyint,
-- Days
calendar_day_in_year smallint,
calendar_day_in_week tinyint, -- The first of the month
calendar_day_in_month tinyint,
mdy_name_long varchar(30),
mdy_name_long_with_suffix varchar(30),
day_name_long varchar(10),
day_name_short varchar(10),
-- Continuous Y/M/D, starts with the first day = 1 and keeps going. Used for various dateadd functions.
continuous_year tinyint,
continuous_quarter smallint,
continuous_month smallint,
continuous_week smallint,
continuous_day int,
-- Custom
description varchar(100),
is_weekend tinyint, -- Tinyint and not bit so you can add the 1's.
is_holiday tinyint, -- Tinyint and not bit so you can add the 1's.
is_workday tinyint, -- Tinyint and not bit so you can add the 1's.
is_event tinyint) -- Used to indicate any special event days.
GO
-- Create the table, with dates ranging from 2010 to 2020. Change to suit your needs.
Declare @dt_start date = '2000-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end)
WHILE @i <= @total_days
begin
INSERT INTO days (PKDate)
SELECT CAST(DATEADD(d, @i, @dt_start) as DATE)
SET @i = @i + 1
end
-- These values can be generated with single SQL Server functions
UPDATE days
SET
calendar_year = YEAR(PKDate),
calendar_quarter = DATEPART(q, PKDate),
calendar_month = DATEPART(m, PKDate),
calendar_week_in_year = DATEPART(WK, PKDate),
calendar_day_in_year = DATEPART(dy, PKDate),
calendar_day_in_week = DATEPART(Weekday, PKDate),
calendar_day_in_month = DATEPART(d, PKDate),
day_name_long = datename(weekday, PKDate)
-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET
is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END,
calendar_quarter_desc = 'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)),
calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', ' + CAST(calendar_year as CHAR(4)),
day_name_short = LEFT(datename(weekday, PKDate),3)
UPDATE days
SET calendar_week_in_month =
CASE
WHEN calendar_day_in_month BETWEEN 1 AND 7 THEN 1
WHEN calendar_day_in_month BETWEEN 8 AND 14 THEN 2
WHEN calendar_day_in_month BETWEEN 15 AND 21 THEN 3
WHEN calendar_day_in_month BETWEEN 22 AND 28 THEN 4
ELSE 5
END
-- Month name: The first three letters of the month.
UPDATE days
SET calendar_month_name_short = LEFT(DATENAME(month, PKDate),3)
/*
-- If the above doesn't work and you want to spell it out, then use this..
UPDATE days
SET calendar_month_name_short = CASE calendar_month
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'June'
WHEN 7 THEN 'July' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END
*/
-- Fancy schmancy full date column, which adds the suffix st, nd, rd, or th to the day.
UPDATE days
SET mdy_name_long_with_suffix = CASE RIGHT(CAST(calendar_day_in_month as varchar(2)), 1)
WHEN '1' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'st, ' + CAST(calendar_year as CHAR(4))
WHEN '2' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'nd, ' + CAST(calendar_year as CHAR(4))
WHEN '3' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'rd, ' + CAST(calendar_year as CHAR(4))
ELSE DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'th, ' + CAST(calendar_year as CHAR(4)) END
-- CONTINUOUS YEARS
UPDATE days
SET
continuous_year = DATEDIFF(year, @dt_start, PKDate) + 1,
continuous_quarter = DATEDIFF(quarter, @dt_start, PKDate) + 1,
continuous_month = DATEDIFF(month, @dt_start, PKDate) + 1,
continuous_week = DATEDIFF(week, @dt_start, PKDATE) + 1,
continuous_day = DATEDIFF(day, @dt_start, PKDATE) + 1
-- HOLIDAYS
-- HOLIDAYS
-- HOLIDAYS
-- Fixed holidays which are always on the same date of every year, Monday through Friday.
-- HOLIDAYS WHERE THE DAY IS ALWAYS THE SAME
-- Fixed Holidays, Mondays through Fridays.
-- (see http://www.cute-calendar.com/category/federal-holidays-in-the-united-states.html)
UPDATE days
SET is_holiday = 1, description = 'American Martin Luther King Jr. Birthday' -- third Monday in January
WHERE calendar_month = 1 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Presidents Day ' -- third Monday in February
WHERE calendar_month = 2 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Labor Day' -- first Monday of September
WHERE calendar_month = 9 AND calendar_week_in_month = 1 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Columbus Day' -- second Monday in October
WHERE calendar_month = 10 AND calendar_week_in_month = 2 AND calendar_day_in_week = 2
UPDATE days
SET is_holiday = 1, description = 'American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 5)
UPDATE days
SET is_holiday = 1, description = 'Day after American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 6)
-- Fixed Holidays by specific date, such as July 4th, but if on a weekend would force either the previous Friday or following Monday to be the holiday.
-- HOLIDAYS WHERE THE DAYS MAY CHANGE
UPDATE days
SET is_holiday = 1, description = 'New Year''s Day' -- - January 1st
WHERE (calendar_month = 1 AND calendar_day_in_month = 1) -- 1/1 when 1/1 is Monday through Friday
UPDATE days
SET is_holiday = 1, description = 'New Year''s Day observed' -- - January 1st
WHERE
(calendar_month = 12 AND calendar_day_in_month = 31 AND calendar_day_in_week = 6) OR -- Friday 12/31 when 1/1 is Saturday
(calendar_month = 1 AND calendar_day_in_month = 2 AND calendar_day_in_week = 2) -- Monday 1/2 when 1/1 is Sunday
-- American Independance Day - July 4th – Or if 7/4 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days
SET is_holiday = 1, description = 'American Independance Day'
WHERE (calendar_month = 7 AND calendar_day_in_month = 4)
UPDATE days
SET is_holiday = 1, description = 'American Independance Day observed'
WHERE
(calendar_month = 7 AND calendar_day_in_month = 3 AND calendar_day_in_week = 6) OR
(calendar_month = 7 AND calendar_day_in_month = 5 AND calendar_day_in_week = 2)
-- American Veterans Day - November 11th – Or if 11/11 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days
SET is_holiday = 1, description = 'American Veterans Day'
WHERE (calendar_month = 11 AND calendar_day_in_month = 11)
UPDATE days
SET is_holiday = 1, description = 'American Veterans Day observed'
WHERE (calendar_month = 11 AND calendar_day_in_month = 10 AND calendar_day_in_week = 6) OR
(calendar_month = 11 AND calendar_day_in_month = 12 AND calendar_day_in_week = 2)
-- Christmas - December 24th and 25th - Or if one or the other falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days
SET is_holiday = 1, description = 'Christmas'
WHERE (calendar_month = 12 AND calendar_day_in_month = 25)
UPDATE days
SET is_holiday = 1, description = 'Christmas observed'
WHERE (calendar_month = 12 AND calendar_day_in_month = 24 AND calendar_day_in_week = 6) OR
(calendar_month = 12 AND calendar_day_in_month = 26 AND calendar_day_in_week = 2)
-- Variable Holidays, such as 'The last Monday in May' (thanks to expert aflcio-hit http://www.experts-exchange.com/members/aflcio-hit.html )
-- Memorial Day
UPDATE days
SET is_holiday = 1, description = 'Memorial Day' -- last Monday in May --select *
FROM days
JOIN (
SELECT max(calendar_week_in_month) WkInMonth , year(pkdate) as Yr
FROM days d
WHERE d.calendar_month = 5 and (calendar_day_in_week = 2)
GROUP BY year(PKDate)) D on D.WkInMonth = days.calendar_week_in_month and D.Yr = year(pkdate) and days.calendar_month = 5 AND calendar_day_in_week = 2 -- Monday
-- Set the non-holiday days
UPDATE days SET is_holiday = 0 WHERE is_holiday IS NULL
UPDATE days SET is_workday = CASE WHEN is_weekend = 0 AND is_holiday = 0 THEN 1 ELSE 0 END
-- Client-defined special events, which I'm defining as not a workday or a holiday,
-- but a day of interest that the client may want to track for data analysis.
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp (PKDate date, description varchar(100))
INSERT INTO #tmp (PKDate, description)
VALUES
('2015-01-02', 'Frank Caliendo'),
('2015-01-05', 'Rock of the 80''s'),
('2015-01-15', 'Smokey Robinson Pre-Show Wine & Smoked Cheese Tasting'),
('2015-01-16', 'Smokey Robinson'),
('2015-01-22', 'Rewind Fest 2015: Psychedelic Furs and More'),
('2015-02-04', 'ZZ Top with Special Guest Blackberry Smoke'),
('2015-02-07', 'Block Party With Kool & The Gang And More'),
('2015-02-13', 'Thunder Vibes Reggae Festival'),
('2015-02-14', 'Michael McDonald and Boz Scaggs'),
('2015-02-16', 'Marco Antonio Solis Y Camilia - La Experiencia Tour'),
('2015-02-26', 'Gladiator Challenge: Collision Course'),
('2015-02-27', 'Rick Springfield'),
('2015-03-04', 'The Thunder Down Under Australian Nudie Revue'),
('2015-03-05', 'The Cosplay Five Sings The Hits'),
('2015-03-10', 'Strawberry Music Fest - Amy''s Orchid Late Nite Lounge'),
('2015-03-27', 'Kenny G: The Rock Opera'),
('2015-03-28', 'Purple Ones - Tribute to the Music of Prince'),
('2015-03-31', 'Steel Slinky - Party Band'),
('2015-04-05', 'Chains Required - Horn driven R&B'),
('2015-04-17', 'Cheezy Poofs = A Dash of alt-rock with soul and funk'),
('2015-04-23', 'Rewind Fest 2015: Psychedelic Furs and More'),
('2015-04-24', 'Shaq''s All-Star Comedy Jam'),
('2015-05-15', 'Peppermint Patty and Mustafa''s All Star Jamacian Steel Drum Band'),
('2015-05-21', 'IFC Caged Combat'),
('2015-06-15', 'The Fabulous Jewish Magician Signumd J. Goldstein'),
('2015-06-30', 'Pearl City Marathon'),
('2015-07-20', 'Tough Mudder'),
('2015-09-05', 'Huey Lewis and The News' ),
('2015-09-18', 'Sarah Colonna and feature act Jeff Bodart'),
('2015-09-19', 'Sarah Colonna and feature act Jeff Bodart'),
('2015-09-12', 'Duc Huy: 50 Years of Love and Music'),
('2015-09-25', 'ABBACADABRA–The Ultimate ABBA Tribute'),
('2015-09-26', 'Last Comic Standing Live Tour'),
('2015-09-09', 'America''s Got Talent Live: The All-Stars Tour!'),
('2015-10-29', 'Flashdance – The Musical'),
('2015-10-10', 'Kenny Rogers' ),
('2015-10-31', 'Sinners & Saints Halloween Party'),
('2015-11-20', 'Donny & Marie Celebrating the Holidays'),
('2015-11-21', 'Donny & Marie Celebrating the Holidays')
-- Set the days with events
UPDATE d
SET d.is_event = 1, d.Description = t.description
FROM #tmp t
JOIN days d ON t.PKDate = d.PKDate
-- Set the days without events
UPDATE days SET is_event = 0 WHERE is_event IS NULL
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (29)
Commented:
Commented:
Commented:
Thanks for the script. What if we want to show the First Day of Week i.e Monday's date.
Regards,
Jag
Commented:
Author
Commented:View More