Santa wants a day off

Right now, using MS SQL 2k, we use the following query to get "number of business days".

case
when DATEDIFF ( dd , dbo.table_case.creation_time , dbo.table_close_case.close_date )
- DATEDIFF ( wk, dbo.table_case.creation_time , dbo.table_close_case.close_date )*2 <0 then 0
else DATEDIFF ( dd , dbo.table_case.creation_time , dbo.table_close_case.close_date )
- DATEDIFF ( wk, dbo.table_case.creation_time , dbo.table_close_case.close_date )*2
end

It doesn't really do anything except for subtract weekends. But instead of being happy with that, the Boss says "Ok, now pull out holidays as well". Any thoughts on how to approach that?I'd say that this is not easy to implement worldwide, because holidays in your country are probably different than holidays in my country, which are again different from ... etc.

The only way I see (until someone enlightens me) is to create a table which will store holiday dates and include this table into your query, such as

... AND date_column NOT IN (SELECT date_column FROM holiday_table)|||Ive alwas found the problem with working day calendars is at what level you identify holidays - do you create a personal calendar, where it is different for each employee, or do you use an overall company calendar. do you care about personal holidays, or just national holidays and weekends

A very effective calendar implementation I saw 20+ years ago worked at unit level. effectively it was a flat file which each month represented by 31 x 4 digit values, each value reprsenting that day working day. If it was a holiday or weekend it took the next working day value. it was used for a production control system. so to find when a part should be scheduled you read the 4 digit working day value from the calendar and did what you required

eg
required start date=required date-time to produce
forecastcompletion date=timenow+time left on job + delays
...etc

Becasue we were using flat files (No SQL) we a few other columns to ease locating the correct date using indexed lookups, and also a mechanism for storing a production week