[dba-SQLServer] Holidays by Nation and State/Province/Canton/etc.

Hi Arthur:

The holidays for a specific region can be easily calculated and automated
from years to year. The only really difficult calculation seems to be
Easter. The problem that I found was that every region has it own way of
interpreting holidays. North America alone would take a fair bit of research
and internationally it could be a career move.

The way I have traditionally handled holidays is to have a trigger, that
rerun a section of code that repopulates the holiday table, at the beginning
of the year and then various apps can refer to the definitive list. 

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
artful at rogers.com
Sent: Thursday, January 11, 2007 7:34 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Holidays by Nation and State/Province/Canton/etc.

One assumes that BusinessDaysBetween (@d1, @d2) observes the Holidays table
and also ignores Saturday and Sunday (which admittedly could be wrong, but
let's at first keep it simple).

So:
Inputs are 01-01-2007 and 02-28-2007. 
Assume that Saturday and Sunday are not business days. (This subject to
revision, but later.)
Assume that there are two holidays in January, the 16th and the 25th (chosen
arbitrarily, I grant you, although I made sure they weren't Saturday or
Sunday, else the exercise would be pointless).

Algorithm:
Build a temp table consisting of the dates between x and y.
Subtract the dates existing in our holidays table.
Subtract the dates describing our weekends.
Sum the remaining days. Return the count.

Holes in the algorithm? Please point them out before I cast this in stone.

TIA,
Arthur


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com