## Calculate Holidays

 Author Message PaladinTech SSC Veteran Group: General Forum Members Points: 246 Visits: 185 Comments posted to this topic are about the item Calculate Holidays Hawkeye67 hgen SSC-Enthusiastic Group: General Forum Members Points: 138 Visits: 88 select * from dbo.GetHolidays(16)ItemID Holiday_Title Holiday_Date1 Easter Sunday 2016-04-102 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_Date1 Easter Sunday 2016-03-27 2 Thanksgiving 2016-11-24 3 Memorial Day 2016-05-30 4 Labor Day 2016-09-05 george.p Mr or Mrs. 500 Group: General Forum Members Points: 541 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 Say Hey Kid Group: General Forum Members Points: 662 Visits: 171 An alternative way of doing itbut 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 )ASBEGIN 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) RETURNEND` Iwas Bornready SSC-Dedicated Group: General Forum Members Points: 38686 Visits: 886 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 SSC Veteran Group: General Forum Members Points: 246 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.PaladinTechI don't understand why I am called a "rookie" when I have been programming SQL Server since the introduction of version 6.5? Hawkeye67