Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate Holidays Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, May 5, 2008 3:35 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, July 5, 2016 2:25 PM Points: 37, Visits: 185
 Comments posted to this topic are about the item Calculate Holidays Hawkeye67
Post #495317
 Posted Thursday, November 24, 2016 3:13 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, November 24, 2016 3:07 AM Points: 134, 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
Post #1837614
 Posted Thursday, November 24, 2016 4:57 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, December 2, 2016 1:42 AM Points: 110, Visits: 223
 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!
Post #1837625
 Posted Thursday, November 24, 2016 8:36 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, December 6, 2016 3:41 AM Points: 146, Visits: 85
 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`
Post #1837669
 Posted Monday, November 28, 2016 8:43 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 6:45 AM Points: 8,328, Visits: 858
 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.
Post #1838387

 Permissions