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

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.

Answer 2

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 3

Suppose you have a table called AllDays, which has columns called theDay and IsPublicHoliday. Also suppose your @@DATEFIRST is set to 1, so that your weekends are days 1 and 7. You want to find the date which is @n days on from @StartDate.

WITH NumberedDays AS
(
SELECT theDay, ROW_NUMBER() OVER (ORDER BY theDay) AS DayNum
FROM AllDays
WHERE DATEPART(dw, theDay) NOT IN (1,7)
AND IsPublicHoliday = 0
AND theDay > @StartDate
)
SELECT theDay
FROM NumberedDays
WHERE DayNum = @n
;

If you don't have a table called AllDays, then you can easily use a table of numbers, where theDay is DATEADD(day,num,@StartDate). You could do a LEFT JOIN to your list of ineligible days (which should be indexed, of course).

Answer 4

@Stuart: Yes, 50 days is a type =P. I meant 50 years.

@rexem: That's true. The calculations would be too complex and error-prone.

@David: Creating a select-only master calendar table and then copying the dates I need per project sounds like a great idea.

(pardon, my twitter-like response, haha)

Thanks for the great responses!