artful at rogers.com
artful at rogers.com
Fri Jan 12 16:05:59 CST 2007
Thanks for that input. But as far as I know the calculation for Easter is quite simple. The first Sunday after the first full moon in April. ----- Original Message ---- From: Jim Lawrence <jlawrenc1 at shaw.ca> To: dba-sqlserver at databaseadvisors.com Sent: Friday, January 12, 2007 4:15:18 PM Subject: Re: [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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com