artful at rogers.com
artful at rogers.com
Fri Jan 12 09:52:31 CST 2007
Yes. Only thing you overlooked is that a given company might operate in several jurisdictions (counties, states, countries) where the Holiday Rules change, so we also need a table defining the jurisdictions in which the holidays apply. However, your insight is right on the money. I shall work on this and see if there is some way to gather this info from the net or from various listers. Thanks. A. ----- Original Message ---- From: Dan Waters <dwaters at usinternet.com> To: dba-sqlserver at databaseadvisors.com Sent: Friday, January 12, 2007 10:17:16 AM Subject: Re: [dba-SQLServer] Holidays by Nation and State/Province/Canton/etc. Hi Arthur! This is my method: Have a permanent table that contains Holidays. Because each company has their own Holiday schedule, they will need to be able to populate this. 1) Calculate days between dates. 2) Subtract weekend days. 3) Subtract any Holidays that fall between the two dates. That's it! Dan Waters -----Original Message----- 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