Calculate Date by number of working days from a certain Startdate

Question

I've got two tables, a project table and a calendar table. The first containts a startdate and days required. The calendar table contains the usual date information, like date, dayofweek, and a column is workingday, which shows if the day is a saturday, sunday, or bank holiday (value = 0) or a regular workday (value = 1).

For a certain report I need write a stored procedure that calculates the predicted enddate by adding the number of estimated workddays needed.

Example:

**Projects**
Name         Start_Planned  Work_days_Required
Project A    02.05.2016     6

Calendar (04.05 is a bank holdiday)
Day          Weekday        Workingday
01.05.2016   7              0
02.05.2016   1              1    
03.05.2016   2              1
04.05.2016   3              0
05.05.2016   4              1
06.05.2016   5              1
07.05.2016   6              0
08.05.2016   7              0
09.05.2016   1              1
10.05.2016   2              1

Let's say, the estimated number of days required is given as 6 (which leads to the predicted enddate of 10.05.2016). Is it possible to join the tables in a way, which allows me to put something like

select date as enddate_predicted
from calendar
join projects
where number_of_days = 6

I would post some more code, but I'm quite stuck on how where to start.

Thanks!

Answer 1

You could get all working days after your first date, then apply ROW_NUMBER() to get the number of days for each date:

SELECT  Date, DayNum = ROW_NUMBER() OVER(ORDER BY Date)
FROM    Calendar
WHERE   IsWorkingDay = 1
AND     Date >= @StartPlanned

Then it would just be a case of filtering for the 6th day:

DECLARE @StartPlanned DATE = '20160502',
        @Days INT = 6;

SELECT  Date
FROM    (   SELECT  Date, DayNum = ROW_NUMBER() OVER(ORDER BY Date)
            FROM    Calendar
            WHERE   WorkingDay = 1
            AND     Date >= @StartPlanned
        ) AS c
WHERE   c.DayNum = @Days;

It's not part of the question, but for future proofing this is easier to acheive in SQL Server 2012+ with OFFSET/FETCH

DECLARE @StartPlanned DATE = '20160502',
        @Days INT = 6;

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @StartPlanned
AND     WorkingDay = 1
ORDER BY Date
OFFSET (@Days - 1) ROWS FETCH NEXT 1 ROWS ONLY

ADDENDUM

I missed the part earlier about having another table, and the comment about putting it into a cursor has prompted me to amend my answer. I would add a new column to your calendar table called WorkingDayRank:

ALTER TABLE dbo.Calendar ADD WorkingDayRank INT NULL;
GO
UPDATE  c
SET     WorkingDayRank = wdr
FROM    (   SELECT  Date, wdr = ROW_NUMBER() OVER(ORDER BY Date)
            FROM    dbo.Calendar
            WHERE   WorkingDay = 1
        ) AS c;

This can be done on the fly, but you will get better performance with it stored as a value, then your query becomes:

SELECT  p.Name,
        p.Start_Planned,
        p.Work_days_Required,
        EndDate = c2.Date
FROM    Projects AS P
        INNER JOIN dbo.Calendar AS c1
            ON c1.Date = p.Start_Planned
        INNER JOIN dbo.Calendar AS c2
            ON c2.WorkingDayRank = c1.WorkingDayRank + p.Work_days_Required - 1;

This simply gets the working day rank of your start date, and finds the number of days ahead specified by the project by joining on WorkingDayRank (-1 because you want the end date inclusive of the range)

This will fail, if you ever plan to start your project on a non working day though, so a more robust solution might be:

SELECT  p.Name,
        p.Start_Planned,
        p.Work_days_Required,
        EndDate = c2.Date
FROM    Projects AS P
        CROSS APPLY
        (   SELECT  TOP 1 c1.Date, c1.WorkingDayRank
            FROM    dbo.Calendar AS c1
            WHERE   c1.Date >= p.Start_Planned
            AND     c1.WorkingDay = 1
            ORDER BY c1.Date
        ) AS c1
        INNER JOIN dbo.Calendar AS c2
            ON c2.WorkingDayRank = c1.WorkingDayRank + p.Work_days_Required - 1;

This uses CROSS APPLY to get the next working day on or after your project start date, then applies the same join as before.

Answer 2

This query returns a table with a predicted enddate for each project

select name,min(day) as predicted_enddate from (
    select c.day,p.name from dbo.Calendar c
    join dbo.Calendar c2 on c.day>=c2.day
    join dbo.Projects p on p.start_planned<=c.day and p.start_planned<=c2.day
    group by c.day,p.work_days_required,p.name
    having sum(c2.workingday)=p.work_days_required
) a
group by name