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

  • I recently needed to calculate Thanksgiving. One of the more valuable resources that I found calculated several US holidays but had faulty logic when it came to Thanksgiving. Instead of trying to fix the logic, I started from scratch and added comments.

    I'll probably take the original script and convert it to a stored proc which then inserts the holidays into the table that we use.


    DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, @Date)), @Date)))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, @Date)), @Date)))))

    PRINT '--- Days to end of week ---'
    PRINT 7-DatePart(dw, '11/1/2014')
    PRINT 7-DatePart(dw, '11/1/2015')
    PRINT 7-DatePart(dw, '11/1/2016')
    PRINT 7-DatePart(dw, '11/1/2017')
    PRINT 7-DatePart(dw, '11/1/2018')
    PRINT 7-DatePart(dw, '11/1/2019')


    PRINT '--- Calculate weekending date that the 1st falls in ---'
    PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')
    PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')
    PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')
    PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')
    PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')
    PRINT DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')

    PRINT '--- Calculate the Thursday before the 1st ---'
    PRINT '--- Yes, we jump forward to Saturday and then jump back to Thursday. It seemed easier given that the math will always give us a positive number. If we use 5 (Thursday), the math could return a positive or negative number that would have to be adjusted. ---'
    PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))
    PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))
    PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))
    PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))
    PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))
    PRINT DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))

    PRINT '--- Add 21 Days to get the Thursday that occurs in 4 weeks ---'
    PRINT '--- This will either be Thanksgiving or the Thursday prior dependeing on the day of the week that the 1st falls under. We adjust for this in the next step. ---'
    PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))
    PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))
    PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))
    PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))
    PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))
    PRINT DateAdd(d, 21, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))

    PRINT '--- Add in another 7 days for each month where the Thursday Prior is in a different month ---'

    PRINT '--- Get the month of the Thursday prior to the first ---'
    PRINT '--- This will be used to determine if another 7 days needs to be added ---'
    PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))
    PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))
    PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))
    PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))
    PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))
    PRINT Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))

    PRINT '--- Calculate the additional 7 days to add in by getting the difference between the month that the prior Thursday falls in (October) and November---'
    PRINT '--- If the prior Thursday is in November, the math will give us 0 which when multiplied by 7 will gives us 0 days to add in---'
    PRINT '--- If the prior Thursday is in October, the math will give us 1 which will give us 7 days to add in ---'
    PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))) * 7
    PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))) * 7
    PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))) * 7
    PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))) * 7
    PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))) * 7
    PRINT (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))) * 7

    PRINT '--- Bring it all home and do not even try to understand this ---'
    PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2014')), '11/1/2014')))))
    PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2015')), '11/1/2015')))))
    PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2016')), '11/1/2016')))))
    PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2017')), '11/1/2017')))))
    PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2018')), '11/1/2018')))))
    PRINT DateAdd(d, (21 + (11 - Datepart(m, (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))) * 7), (DateAdd(d, -2, (DateAdd(d, 7-(DatePart(dw, '11/1/2019')), '11/1/2019')))))
    PRINT '--- You have just eaten an elephant ---'
  • This was removed by the editor as SPAM

  • Here is something - just for a different perspective:

      CREATE Function [dbo].[fnGetUSHolidays] (
    @inputYear int = 1792
    )
    Returns Table With schemabinding
    As
    Return

    /* ===========================================================================================
    Author: Jeff Williams
    Created: 10/17/2019
    Description: Returns a list of defined holidays for the input year

    To determine the holidays, we use a list of holiday definitions:

    Observed 0 = not observed
    1 = is observed (sets IsHoliday Flag)
    2 = observed on fixed date only (no offset - sets IsHoliday Flag)
    MonthNumber the month where the holiday occurs
    DayNumber the day of the holiday (fixed holidays)
    WeekDayNumber the weekday of the holiday (0 = Monday, 6 = Sunday)
    Occurrence the occurrence in the month, -1 is last occurrence in month
    AddDays day offset to be added to the calculation
    HolidayName the name of the holiday

    For Holidays that are defined on a specific day (New Year's, Christmas), check
    if that day falls on a weekend. If the day falls on a weekend, offset the day
    to the nearest week day and mark that day as a holiday in addition to the actual
    holiday.

    For fixed holidays that are only observed on the specific day (ex: Halloween), use
    2 for Observed and the weekend offset will not be calculated. Weekend offsets will
    not be calculated for any non-observed holidays.

    Ex. If New Year's Day falls on a Saturday for the given year, define 12/31
    of the previous year as a holiday - append (observed) to the name and
    add this date to the list of holidays for that year.

    For Holidays that are calculated as the next day, for example
    'Day after Thanksgiving' - calculate as the Holiday and use AddDays to adjust for
    the correct date.

    Called From:
    Calendar Dim Function and direct queries

    Example Calls:
    Select * From dbo.fnGetUSHolidays(0001);
    Select * From dbo.fnGetUSHolidays(2020);

    Revision History
    Date Edited By Change
    ---------- --------------- --------------------------------------------------------------
    10/17/2019 Jeff Williams Created
    =========================================================================================== */

    With holidays
    As (
    Select h.Observed
    , MonthNumber = cast(h.MonthNumber As int)
    , DayNumber = cast(h.DayNumber As int)
    , Occurrence = cast(h.Occurrence As int)
    , AddDays = cast(h.AddDays As int)
    , HolidayName = cast(h.HolidayName As varchar(25))
    , FirstOfMonth = datefromparts(@inputYear, h.MonthNumber, 7)
    , WeekDayDate = dateadd(day, h.WeekDayNumber, cast('0001-01-01' As date))
    From (
    Values (1, 01, 01, Null, Null, Null, 'New Year''s Day') -- WeekDayNumber: 0 = Monday
    , (1, 01, Null, 0, 3, 0, 'Martin Luther King Day') -- 1 = Tuesday
    , (1, 02, Null, 0, 3, 0, 'President''s Day') -- 2 = Wednesday
    , (2, 03, 17, Null, Null, Null, 'St Patrick''s Day') -- 3 = Thursday
    , (0, 05, Null, 6, 2, 0, 'Mother''s Day') -- 4 = Friday
    , (1, 05, Null, 0, -1, 0, 'Memorial Day') -- 5 = Saturday
    , (0, 06, Null, 6, 3, 0, 'Father''s Day') -- 6 = Sunday
    , (1, 07, 04, Null, Null, Null, 'Independence Day')
    , (1, 09, Null, 0, 1, 0, 'Labor Day') -- Occurrence: -1 = Last day in the month
    , (1, 10, Null, 0, 2, 0, 'Columbus Day') -- n = # day in the month
    , (0, 10, 31, Null, Null, Null, 'Halloween') -- (2nd Monday, 4th Thursday)
    , (1, 11, 11, Null, Null, Null, 'Veterans Day')
    , (1, 11, Null, 3, 4, 0, 'Thanksgiving Day') -- AddDays: n = Add # days to calculation
    , (0, 11, Null, 3, 4, 1, 'Day after Thanksgiving')
    , (1, 12, 25, Null, Null, Null, 'Christmas Day')
    ) As h(Observed, MonthNumber, DayNumber, WeekDayNumber, Occurrence, AddDays, HolidayName)
    )
    Select IsHoliday = cast(h.Observed As bit)
    , obs.HolidayName
    , obs.HolidayDate
    From holidays As h

    Cross Apply (Values (eomonth(datefromparts(@inputYear, h.MonthNumber, 1))
    , (datefromparts(@inputYear, h.MonthNumber, h.DayNumber)))) As dd(eom, FixedHoliday)
    Cross Apply (Values (dateadd(day, datediff(day, h.WeekDayDate, h.FirstOfMonth) / 7 * 7, h.WeekDayDate))) As mm(FirstDayInMonth)
    Cross Apply (Values (coalesce(nullif(h.Occurrence, -1), datediff(day, mm.FirstDayInMonth, dd.eom) / 7 + 1) - 1)) As dm(Occurrence)

    Cross Apply (
    Select HolidayDate = coalesce(ob.ObservedDate, dateadd(day, dm.Occurrence * 7 + h.AddDays, mm.FirstDayInMonth))
    , HolidayName = concat(h.HolidayName, iif(dd.FixedHoliday <> ob.ObservedDate, ' (observed)', ''))
    From (Values (datediff(day, cast('0001-01-01' As date), dd.FixedHoliday) % 7)) As dt(dow)
    Cross Apply (Values (dateadd(day, -1, dd.FixedHoliday), (dt.dow / 5) & ~(dt.dow / 6) & h.Observed)
    , (dd.FixedHoliday, 1), (dateadd(day, 1, dd.FixedHoliday), (dt.dow / 6) & h.Observed)) As ob(ObservedDate, IsObserved)
    Where ob.IsObserved = 1
    ) As obs;
    GO

    This can be used directly - or used to populate a Calendar table.  To work with US bank holidays - remove the first row from ob (Observed) to remove the previous Friday when the holiday falls on a Saturday.

    You can also review this: https://www.sqlservercentral.com/scripts/calculating-easter-in-sql for several ways to calculate Easter.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 9 posts - 1 through 8 (of 8 total)

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