Exclude weekends and custom days (i.e. Holidays) from date calculations

Question

Currently, I am calculating a finish date based on the start date (DateTime) and duration (# of days), but my calculations do not take into account weekends or holidays.

So, my solution is not correct. This was just a starting point.

I read some articles out there and one approach is to create a gigantic calendar table that has all the weekends and holidays for the next 50 years. I guess the idea is to query a date range against the calendar table and subtract the # of weekends and/or holidays.

The problem is the software I am working on allows users to set their own project calendar. Wouldn't the table become to large to maintain given that the software allows users to manage multiple projects?

So, I guess my question how do I get started and what are some possible approaches to this problem?

Basically, for each project task I need to calculate the task finish date given the START date and DURATION, but taking weekends and custom days(aka holidays) into consideration.

Any ideas?

BTW: I'm using SQL Server 2005.

Answer 1

I read some articles out there and one approach is to create a gigantic calendar table that has all the weekends and holidays for the next 50 years. I guess the idea is to query a date range against the calendar table and subtract the # of weekends and/or holidays.

It's because some holidays don't consistently fall on the same date. Labour day for example - the first Monday of September. It's easier & takes less space in the db to store the date it falls on in a per year basis than to try to code the rules to calculate it.

Other considerations are holidays that land on Sat/Sun - it's a tossup if the day off will fall on Monday or Friday. And some holidays will be federal while others are local...

Answer 2

Create a large calendar that contains all weekends and holidays for the rest of the [long period of time], have it set to select only. Then copy the needed days into the calendar into the project's calendar each time a new project is created.