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)

    ItemIDHoliday_TitleHoliday_Date

    1 Easter Sunday2016-03-27

    2 Thanksgiving2016-11-24

    3 Memorial Day2016-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@FirstDayOfGivenYearAS date_

    , DATEPART(DW, @FirstDayOfGivenYear)AS dw

    , DATEPART(MONTH, @FirstDayOfGivenYear)AS month_

    , DATEPART(WEEK, @FirstDayOfGivenYear)AS week_

    , 1AS ocurrency_in_month

    UNIONALL

    SELECTDATEADD(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

    ENDAS ocurrency_in_month

    FROMDATES

    WHEREdate_ < @LastDayOfGivenYear

    )

    , DATES_2AS (

    SELECTD.date_

    , D.dw

    , D.month_

    , D.week_

    , D.ocurrency_in_month

    , M.max_ocurrency_in_month

    FROMDATESD

    INNERJOIN

    (

    SELECTmonth_

    , dw

    , MAX(ocurrency_in_month)AS max_ocurrency_in_month

    FROMDATES

    GROUPBY month_, dw

    )M

    ONM.month_ = D.month_

    andM.dw = D.dw

    )

    , HOLLIDAYS AS (

    SELECT'Easter Sunday'AS Holiday_Title

    , @EasterAS Holiday_Date

    UNION ALL

    SELECTCASE

    WHEN month_ = 11AND dw = 5AND ocurrency_in_month = 4

    THEN 'Thanksgiving'-- 4º Thursday of November

    WHEN month_ = 9AND dw = 2AND ocurrency_in_month = 1

    THEN 'Labor Day'-- 1º Monday of September

    WHEN month_ = 5AND dw = 2AND ocurrency_in_month = max_ocurrency_in_month

    THEN 'Memorial Day'-- Last Monday of May

    ENDAS Holiday_Title

    , date_AS Holiday_Date

    FROMDATES_2

    )

    INSERTINTO @Holidays (Holiday_Title, Holiday_Date)

    SELECTHoliday_Title, Holiday_Date

    FROMHOLLIDAYS

    WHEREHoliday_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