Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate Holidays Expand / Collapse
Author
Message
Posted Monday, May 5, 2008 3:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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_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
Post #1837614
Posted Thursday, November 24, 2016 4:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 6, 2016 3:41 AM
Points: 146, Visits: 85
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

Post #1837669
Posted Monday, November 28, 2016 8:43 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse