Calculate Holidays

  • Comments posted to this topic are about the item Calculate Holidays

    Hawkeye67

  • 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

  • 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!

  • 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

  • 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.

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply