|
Please allow me to suggest that there is a much easier way.
Rather than storing a list of holidays with complicated formulae like
first Monday and so on, and then trying to figure out if a given
date matches any of the stored holidays, consider building
a calendar table.
For example:
caldate wkday holiday office_open
2007-04-04 4 NULL 1
2007-04-05 5 NULL 1
2007-04-06 6 Good Friday 0
2007-04-07 7 NULL 0
2007-04-08 1 NULL 0
2007-04-09 2 NULL 0
2007-04-10 3 NULL 1
2007-04-11 4 NULL 1
In this example, Friday April 6, 2007 is a holiday (Good Friday),
and the office is not open. The office is also not open on Saturday
or Sunday, as usual, but neither is it open on the following Monday.
There are two advantages. Firstly, your SQL will be mean and lean,
because all you need to do is join to the calendar table, or do a simple
lookup query. The query to find the number of business days between
two dates is trivial; just SUM the office_open column. Secondly,
you will be able to record special events for your particular
office, even if they aren't normal holidays.
How to create this calendar table? Manually. It takes only a few
minutes every year to prepare the data. I like to use Excel because
it's easy to use Edit>Fill>Series to populate the date column,
as well as copy/paste for repeating data like the wkday values. Then
you can easily forward the worksheet to HR or Accounting, to have them
verify the accuracy of the holidays and office closings for your company
before you load the data into the database.
|
