CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Abe
Abe

Reputation: 6516

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

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.

Upvotes: 3

Views: 16633

Answers (6)

Kundan
Kundan

Reputation: 1

Use below code to get next wrking date after excluding weekends and Holidays

Declare @AddDay as integer = 3
Declare @NextWorkingDate  DateTime
Declare @StartDate  DateTime = Cast(getdate() as date)

While  @AddDay > 0 
    begin

        Select @NextWorkingDate =  @StartDate + @AddDay +
        (datediff(wk, @StartDate, @StartDate+ @AddDay  ) * 2) -- add weekend 

        --Exclude weekend
        If datepart(dw,@NextWorkingDate ) = 1 or datepart(dw,@NextWorkingDate ) = 7  --Add 2 days if target date is either Saturday or Sunday
            set @NextWorkingDate = @NextWorkingDate + 2 

        --Count no of holidays if falling within start date and nextwrking date
        Select @AddDay = Count(*)  from HolidayTable ST --Holiday list
                    where ST.OffDate between @StartDate+1 and @NextWorkingDate
        Set @StartDate = @NextWorkingDate
    End         

Select @NextWorkingDate

Upvotes: 0

Joabian
Joabian

Reputation: 13

Hi this is haw I resolve it:

first I created a calendar table (tb_cal) with two fields date_day (smalldate), holiday (bit)

CREATE TABLE [user].[tb_cal](
[date_day] [smalldatetime] NULL,
[holiday] [bit] NULL
) ON [PRIMARY]

then this function:

CREATE FUNCTION [user].[fc_get_labor_days]
(@from datetime, @to datetime)
RETURNS int
AS
BEGIN
return ( 
select count(*) as total 
from tb_cal 
where datepart(dw, date_day) not in (1,7)   
and holiday <> 1 
and date_day > @from and date_day <= @to )
END

you can call this function by passing the parameter (from, to)

SELECT user.fc_get_labor_days(my_date_from, my_date_to) as [days]

Hope this helps

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

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...

Upvotes: 1

Abe
Abe

Reputation: 6516

@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!

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

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).

Upvotes: 0

David Oneill
David Oneill

Reputation: 13065

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.

Upvotes: 1

Related Questions