May 5, 2008 at 3:35 pm
Comments posted to this topic are about the item Calculate Holidays
Hawkeye67
November 24, 2016 at 3:13 am
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
November 24, 2016 at 4:57 am
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!
November 24, 2016 at 8:36 am
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 @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
November 28, 2016 at 8:43 am
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.
December 15, 2016 at 6:09 am
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