SQLTeam.com | Weblogs | Forums

Calculating Start and End Dates From Predecessors in 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)

0 Likes

#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
0 Likes

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

0 Likes

#24

Did you look at my code yet at all?

0 Likes

#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
0 Likes