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

use below logic

CREATE TABLE #proj(Name varchar(50),Start_Planned date, 

Work_days_Required int)

insert into #proj
values('Project A','02.05.2016',6)

CReATE TABLE #Calendar(Day date,Weekday int,Workingday bit)

insert into #Calendar
values('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)


DECLARE @req_day int = 3
DECLARE @date date = '02.05.2016'

--SELECT @req_day = Work_days_Required FROM #proj where Start_Planned = @date

select *,row_number() over(order by [day] desc) as cnt
    from #Calendar
    where Workingday = 1
        and [Day] > @date

SELECT *
FROM
(
    select *,row_number() over(order by [day] desc) as cnt
    from #Calendar
    where Workingday = 1
        and [Day] > @date
)a
where cnt = @req_day

Answer 3

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

Answer 4

--This gives me info about all projects

   select p.projectname,p.Start_Planned ,c.date,
    from calendar c
    join
    projects o
    on c.date=dateadd(days,p.Work_days_Required,p.Start_Planned)
    and   c.isworkingday=1

now you can use CTE like below or wrap this in a procedure

;with cte
as
(
    Select 
      p.projectnam
      p.Start_Planned ,
      c.date,datediff(days,p.Start_Planned,c.date) as nooffdays
        from calendar c
        join
        projects o
        on c.date=dateadd(days,p.Work_days_Required,p.Start_Planned)
         and   c.isworkingday=1
)
select * from cte where nooffdays=6