SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate Holidays


Calculate Holidays

Author
Message
PaladinTech
PaladinTech
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 185
Comments posted to this topic are about the item Calculate Holidays

Hawkeye67
hgen
hgen
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 88
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)

ItemID Holiday_Title Holiday_Date
1 Easter Sunday 2016-03-27

2 Thanksgiving 2016-11-24
3 Memorial Day 2016-05-30
4 Labor Day 2016-09-05
george.p
george.p
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 261
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!
paulo.margarido
paulo.margarido
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 161
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 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 = ((@Year + (@Year / 4) + @i + 2 - @c + (@c / 4)) % 7)
SET @l = @i - @j
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 @FirstDayOfGivenYear AS date_
, DATEPART(DW, @FirstDayOfGivenYear) AS dw
, DATEPART(MONTH, @FirstDayOfGivenYear) AS month_
, DATEPART(WEEK, @FirstDayOfGivenYear) AS week_
, 1 AS ocurrency_in_month
UNION ALL
SELECT DATEADD(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
END AS ocurrency_in_month
FROM DATES
WHERE date_ < @LastDayOfGivenYear
)
, DATES_2 AS (
SELECT D.date_
, D.dw
, D.month_
, D.week_
, D.ocurrency_in_month
, M.max_ocurrency_in_month
FROM DATES D
INNER JOIN
(
SELECT month_
, dw
, MAX(ocurrency_in_month) AS max_ocurrency_in_month
FROM DATES
GROUP BY month_, dw
) M
ON M.month_ = D.month_
and M.dw = D.dw
)
, HOLLIDAYS AS (
SELECT 'Easter Sunday' AS Holiday_Title
, @Easter AS Holiday_Date
UNION ALL
SELECT CASE

WHEN month_ = 11 AND dw = 5 AND ocurrency_in_month = 4
THEN 'Thanksgiving' -- 4º Thursday of November
WHEN month_ = 9 AND dw = 2 AND ocurrency_in_month = 1
THEN 'Labor Day' -- 1º Monday of September
WHEN month_ = 5 AND dw = 2 AND ocurrency_in_month = max_ocurrency_in_month
THEN 'Memorial Day' -- Last Monday of May
END AS Holiday_Title
, date_ AS Holiday_Date
FROM DATES_2
)
INSERT INTO @Holidays (Holiday_Title, Holiday_Date)
SELECT Holiday_Title, Holiday_Date
FROM HOLLIDAYS
WHERE Holiday_Title IS NOT NULL
OPTION (MAXRECURSION 0)

RETURN
END


Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21902 Visits: 885
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.
PaladinTech
PaladinTech
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 185
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search