What I have done is create a table called ExceptionDays with
information on the date and start/end times involved. There are also
fields to hold company information. I put all standard holidays into
the table including weekends. Then I add special days that are
specific to a company. For example, in addition to the normal
office hours, some companies let their employees off on Christmas Eve
at 12:00 noon. Furthermore, some companies have business units that
are up 24 hours-a-day. Anyway, I have to set up special functions to
do some fancy calculations depending on the report that may require
office hours vs. 24-hour results.
There are some basic holiday "rules" that can be programmed. National
holidays like July 1 (Can), July 4th (US) or July 14th (Fra) are by
date. Same for religious holidays like Christmas. Easter floats
around depending on when the first full moon after the equinox
happens. Victoria Day (Can) is always the 3rd Monday of May.
Memorial
Day (US) is always the last Monday in May. Labour Day (N. Amer) is
always the first Monday in September. Can/US have different
Thanksgivings.
That doesn't even account for provincial/state/municipal differences
or
corporations that span many jurisdictions. Not simple, but doable if
someone wants to take the time.
SAM
[quoted text, click to view] Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns949D7ECD2E69AYazorman@127.0.0.1>...
> Nils Magnus Englund (nils.magnus.englund@orkfin.no) writes:
> > I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
> > want to select all rows from that table, excluding days which fall on
> > holidays or weekends. What is the best way to accomplish this? I
> > considered creating a new table called "holidays" and then selecting all
> > rows (sort of "where not in (select * from holidays)") , but I was
> > looking for a better solution since that implies that I have to populate
> > the "holidays" table.
>
> And how would you expect SQL Server to know about syttende maj or when
> Midsummer is?
>
> You can of course make the holidays table more or less sophisticated.
> You can just put in all Mondays to Fridays that are not dates from now
> to 2020 or whatever.
>
> You can also write a stored procedure that fills in the table given the
> rules about currently known holidays. You would need to find data on
> where Easter falls, to determine days for Easter, Whitsun and Ascenion Day.
>
> Yet an alternative is to put all days in that table, and then a flag
> whether the day is a working day or not, no matter whether it's Friday
> or Sunday.
>
> And finally, for the SELECT it self I prefer:
>
> SELECT *
> FROM tbl t
> WHERE NOT EXISTS (SELECT *
> FROM holidays h
