Exclude weekends and holidays (e.g. holidays) from date calculation

Question

I am currently calculating the end date based on the start date (DateTime) and duration (number of days), but my calculations do not include weekends or holidays.

So my decision is wrong. It was just a starting point.

I read several articles, and one of them is to create a giant calendar table that has all the weekends and holidays for the next 50 years. I guess the idea is to query the date range against the calendar table and subtract the number of days off and / or holidays.

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

So, I think, my question is, how do I get started and what are the possible approaches to this problem?

In principle, for each project task, I need to calculate the completion date of the task, taking into account the start date and DURATION, but taking into account weekends and user days (for example, holidays).

Any ideas?

BTW: I am using SQL Server 2005.

Answer 1

, - , 50 . , , / .

, . , , - . , , , .

, Sat/Sun - , . , - ...

Answer 2

, , [ ], . , .

Answer 3

, AllDays, Day IsPublicHoliday. , @@DATEFIRST 1, - 1 7. , @n @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
;

AllDays, , DAY - DATEADD (, , @StartDate). LEFT JOIN (, , ).

Answer 4

@Stuart: , 50 - = P. 50 .

@rexem: . .

@David: , , , .

(pardon, - , -)

!

Answer 5

, :

(tb_cal) date_day (smalldate), holiday (bit)

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

:

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 

, (from, to)

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

,

Answer 6

, wrking

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