Calculating Start and End Dates From Predecessors in SQL

Transact-SQL
21

Here is sample data

DECLARE @Test AS TABLE(ActID INT,ActName VARCHAR(50),Duration INT,Predecessor INT)
INSERT INTO @Test VALUES(1,'Building Contract',2,NULL)
INSERT INTO @Test VALUES(2,'Land Survey',2,1)
INSERT INTO @Test VALUES(3,'Soil Testing',3,5)
INSERT INTO @Test VALUES(4,'EMPTY',4,3)
INSERT INTO @Test VALUES(5,'Land Excavation',5,2)

22

The result should be generated with these values ::

Building Contract 2 days Wed 20-03-19 Thu 21-03-19
Land Survey 2 days Fri 22-03-19 Mon 25-03-19 1
Soil Testing 3 days Tue 02-04-19 Thu 04-04-19 5
Empty 4 days Fri 05-04-19 Wed 10-04-19 3
Land Excavation 5 days Tue 26-03-19 Mon 01-04-19 2
23

Another thread of similar question can be found, yet helpful for you people.

www . aspforums . net/Threads/518397/Calculation-mistake-in-predecessors-in-SQL/?s=1

Please check the link and solve this thread.

Thanks.

24

Did you look at my code yet at all?

25

The following changes to my last solution, will give you the output you showed.

Change start date from:

declare @startDate date='2019-03-11';

to:

declare @startDate date='2019-03-20';

Exclude saturdays as well, from:

                    ,case when datediff(day,0,dateadd(day,-1+row_number() over(order by (select null)),@startdate))%7=6 then 1 else 0 end as exclude

to:

                    ,case when datediff(day,0,dateadd(day,-1+row_number() over(order by (select null)),@startdate))%7>=5 then 1 else 0 end as exclude