# Calculate Holidays

• Comments posted to this topic are about the item Calculate Holidays

Hawkeye67

• select * from dbo.GetHolidays(16)

ItemID Holiday_Title Holiday_Date

1 Easter Sunday 2016-04-10

2 Thanksgiving 2016-11-24

3 Memorial Day 2016-05-30

4 Labor Day 2016-09-05

select * from dbo.GetHolidays(2016)

ItemIDHoliday_TitleHoliday_Date

1 Easter Sunday2016-03-27

2 Thanksgiving2016-11-24

3 Memorial Day2016-05-30

4 Labor Day 2016-09-05

• This code is very difficult to understand - there are no supporting comments and a lot of variables named as single letters.

Some documentation of how it works would be useful!

• An alternative way of doing it

but better was to create a calendar table with relevant columns, like the ones in the CTE's in the function, then its just a select to that table

`CREATE FUNCTION dbo.GetHolidays_2(`

`@Year INT = 0`

`)`

`RETURNS @Holidays TABLE(`

`ItemID INT IDENTITY(1,1)`

`, Holiday_Title VARCHAR(50)`

`, Holiday_Date DATETIME`

`)`

`AS`

`BEGIN`

`DECLARE @Month INT`

`, @Day INT`

`, @Easter DATE`

`-- Calculate Easter Sunday`

`-- Alogrithm modeled after VB2TheMax code`

`DECLARE @g INT`

`, @C INT`

`, @h INT`

`, @i INT`

`, @j-2 INT`

`, @l INT`

`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-2 = ((@Year + (@Year / 4) + @i + 2 - @C + (@c / 4)) % 7)`

`SET @l = @i - @j-2`

`SET @Month = 3 + ((@l + 40) / 44)`

`SET @Day = @l + 28 - (31 * (@Month / 4))`

`SET @Easter = DATEADD(DAY,@Day- 1,DATEADD(MONTH,@Month-1,DATEADD(YEAR, (@Year - DATEPART(YEAR, 0)), 0)))`

`DECLARE @FirstDayOfGivenYear DATE`

`, @LastDayOfGivenYear DATE`

`SELECT @FirstDayOfGivenYear = DATEADD(DAY,0,DATEADD(MONTH,0,DATEADD(YEAR, (@Year - DATEPART(YEAR, 0)), 0)))`

`, @LastDayOfGivenYear = DATEADD(DAY,-1,DATEADD(MONTH,0,DATEADD(YEAR, (@Year - DATEPART(YEAR, 0) + 1), 0)))`

`;WITH DATES AS (`

`SELECT@FirstDayOfGivenYearAS date_`

`, DATEPART(DW, @FirstDayOfGivenYear)AS dw`

`, DATEPART(MONTH, @FirstDayOfGivenYear)AS month_`

`, DATEPART(WEEK, @FirstDayOfGivenYear)AS week_`

`, 1AS ocurrency_in_month`

`UNIONALL`

`SELECTDATEADD(DAY, 1, date_)AS date_`

`, DATEPART(DW, DATEADD(DAY, 1, date_))AS dw`

`, DATEPART(MONTH, DATEADD(DAY, 1, date_))AS month_`

`, DATEPART(WEEK, DATEADD(DAY, 1, date_))AS week_`

`, CASE`

`WHEN DATEPART(DAY, DATEADD(DAY, 1, date_)) = 1`

`THEN 1`

`ELSE`

`CASE WHEN DATEPART(DAY, DATEADD(DAY, 1, date_)) IN (8, 15, 22, 29)`

`THEN ocurrency_in_month + 1`

`ELSE ocurrency_in_month`

`END`

`ENDAS ocurrency_in_month`

`FROMDATES`

`WHEREdate_ < @LastDayOfGivenYear`

`)`

`, DATES_2AS (`

`SELECTD.date_`

`, D.dw`

`, D.month_`

`, D.week_`

`, D.ocurrency_in_month`

`, M.max_ocurrency_in_month`

`FROMDATESD`

`INNERJOIN`

`(`

`SELECTmonth_`

`, dw`

`, MAX(ocurrency_in_month)AS max_ocurrency_in_month`

`FROMDATES`

`GROUPBY month_, dw`

`)M`

`ONM.month_ = D.month_`

`andM.dw = D.dw`

`)`

`, HOLLIDAYS AS (`

`SELECT'Easter Sunday'AS Holiday_Title`

`, @EasterAS Holiday_Date`

`UNION ALL`

`SELECTCASE `

`WHEN month_ = 11AND dw = 5AND ocurrency_in_month = 4`

`THEN 'Thanksgiving'-- 4º Thursday of November`

`WHEN month_ = 9AND dw = 2AND ocurrency_in_month = 1`

`THEN 'Labor Day'-- 1º Monday of September`

`WHEN month_ = 5AND dw = 2AND ocurrency_in_month = max_ocurrency_in_month`

`THEN 'Memorial Day'-- Last Monday of May`

`ENDAS Holiday_Title`

`, date_AS Holiday_Date`

`FROMDATES_2`

`)`

`INSERTINTO @Holidays (Holiday_Title, Holiday_Date)`

`SELECTHoliday_Title, Holiday_Date`

`FROMHOLLIDAYS`

`WHEREHoliday_Title IS NOT NULL`

`OPTION (MAXRECURSION 0)`

`RETURN`

`END`

• george.p (11/24/2016)

This code is very difficult to understand - there are no supporting comments and a lot of variables named as single letters.

Some documentation of how it works would be useful!

I agree. Still kind of nice though.

• I just became aware of the fact that SQL Server Central subscribers were still accessing this version of Calculate Holidays. This was written years ago and I have since done many different versions of this, including preparing an actual table of holidays, for my clients. I was also aware of the fact of the difference between using the two digit year and the four digit year. I will try and find my most recent version for SQL Server Central posting.

Sorry for the late response. I am semi-retired and spending a lot of time hunting the "king" of game birds, the Ruffed Grouse, with my new hunting dog.

Take care.

PaladinTech

I don't understand why I am called a "rookie" when I have been programming SQL Server since the introduction of version 6.5? :unsure:

Hawkeye67

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply