Politics makes for bad databases - plus calculate British holidays

  • RevOX11

    SSC Veteran

    Points: 270

    Comments posted to this topic are about the item Politics makes for bad databases - plus calculate British holidays

  • alastair.beveridge

    SSCrazy

    Points: 2359

    You have forgotten that not all of the UK has the same bank holidays.

  • keith.macdonald

    SSC Rookie

    Points: 27

    An excellent article, and nice function code. But maybe it would be more accurate to if it was named something like "Create_fnGetEnglishHolidays" ? Scotland, Wales and Northern Ireland have some different (extra?) days.

    Regarding the calculation for Easter Sunday, people might like to know what it's doing. It's finding the first Sunday after the first full moon after the Vernal Equinox.

    Set @g = @siYear % 19

    Set @C = @siYear / 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 = ((@siYear + (@siYear / 4) + @i + 2 - @C + (@c / 4)) % 7)

    Set @l = @i - @j

    Set @siMonth = 3 + ((@l + 40) / 44)

    Set @siDay = @l + 28 - (31 * (@siMonth / 4))

    Set @strHolidayName = 'Easter Sunday'

    Set @dtHolidayDate = Cast(Cast(@siYear As VarChar(4)) + '/' + Cast(@siMonth As VarChar(2)) + '/' + Cast(@siDay As VarChar(2)) As Date)

  • thisisfutile

    Hall of Fame

    Points: 3486

    We looked at SAP too, along with 14 other software offerings.  Choosing an ERP is no small task, in fact, it's the hardest thing I've ever had to do.  I can't even fathom making such a massive decision based on a few friends saying, "try it".  We too are a small business (14 employees) and it was an 18 month project from start to finish to migrate from one ERP to another.  The project had at least 4 phases, the initial discovery phase being the most important.  This is where we ruled out SAP.  That's not to say it should be ignored by others, heck it was a great product and one that I was initially excited about.  In fact, it made our top 5, it's just that every business is different and the ones we thought were going to be great for us turned out to be missing key components...something that we would have regretted if we had just "tried it".

  • Joffrey

    SSC Enthusiast

    Points: 135

    alastair.beveridge wrote:

    You have forgotten that not all of the UK has the same bank holidays.

    I never thought it was scriptable. Sometimes Easter is end of March, sometimes early April.

    Not ideal, but I have a calendar reminder to revise next year's bank holidays as we also have some custom holidays around Christmas that are not known until the year starts.. Just manually flag the days on a central calendar table.

  • thisisfutile

    Hall of Fame

    Points: 3486

    Joffrey wrote:

    I never thought it was scriptable. Sometimes Easter is end of March, sometimes early April. Not ideal, but I have a calendar reminder to revise next year's bank holidays as we also have some custom holidays around Christmas that are not known until the year starts.. Just manually flag the days on a central calendar table.

    I'm right there with you.  I figure I look at the calendar each year anyway (and I'd do it every year to confirm the script results) so I might as well enter it manually.  Also, I have a separate phone system that's not easily interfaced with so I do it manually too.  It's part of my January 2nd ritual.

  • s.quick

    Newbie

    Points: 9

    Possibly not useful for this situation? But may be helpful/interesting; the UK Govt. have a webservice for bank holidays

    https://www.gov.uk/bank-holidays.json

  • Joffrey

    SSC Enthusiast

    Points: 135

    s.quick wrote:

    Possibly not useful for this situation? But may be helpful/interesting; the UK Govt. have a webservice for bank holidays https://www.gov.uk/bank-holidays.json

    Cheers for that. Something for R&D Fridays 🙂

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

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