# Calculating Start and End Dates From Predecessors in SQL

• ### Question

• SQL

I have the following data in which I have duration and predecessors for my activities. Now I want to calculate the start and end date of activities with reference to their predecessors. The data is sampled below. Also please note there might be activities which may not have predecessors so their start date should be @startDate and end date should be calculated from their duration and sundays should be excluded.

Sample Data

Required Data

Please provide me the solution asap.

Thanks.

Wednesday, March 13, 2019 9:00 AM

• First of all thanks for your solution.

Second, it is generating wrong dates, as it is summing the duration which is not required.

Here's what i have written :

```DECLARE @startdate date = '2019-03-11';

declare @Task table (ActID int, ActivityName nvarchar(100),Duration int, StartDate date, EndDate date,Prededessor int)
select 1, 'Building Contract', 3, null,null,null
union all
select 2, 'Land Survey', 2, null,null,1
union all
select 3, 'Soil Testing', 3, null,null,2
union all
select 4, 'Excavation', 5, null,null,3
union all
select 5, 'Drilling', 4, null,null,null

SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
END,

EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,t.Duration - 1,@Startdate)
END

FROM
(
SELECT StartDate,EndDate,Duration,SUM(Duration) OVER (ORDER BY ActID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CumDur
)t

This is what I get as result using code above

```ActID	ActivityName	Duration	StartDate	EndDate	Prededessor
-------------------------------------------------------------------------------------
1	Building Contract	3	2019-03-11	2019-03-13	NULL
2	Land Survey	2	2019-03-24	2019-03-27	1
3	Soil Testing	3	NULL	NULL	2
4	Excavation	5	2019-03-24	2019-03-27	3
5	Drilling	4	2019-03-11	2019-03-13	NULL```

Can you explain how its correct as per your initial rule?

How did startdate become 2019-03-24 for row with ActID 2? Also if duration is 2 how can end date be 2019-03-27?

It doesnt match the result you were showing in the initial post :)

On the other hand my suggestion looks like this

```DECLARE @startdate date = '2019-03-11';

declare @Task1 table (ActID int, ActivityName nvarchar(100),Duration int, StartDate date, EndDate date,Prededessor int)
select 1, 'Building Contract', 3, null,null,null
union all
select 2, 'Land Survey', 2, null,null,1
union all
select 3, 'Soil Testing', 3, null,null,2
union all
select 4, 'Excavation', 5, null,null,3
union all
select 5, 'Drilling', 4, null,null,null

--My suggestion
UPDATE t
SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
END
FROM
(
SELECT Prededessor,ActID,StartDate,EndDate,Duration,SUM(Duration) OVER (ORDER BY ActID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CumDur
)t

/*

ActID	ActivityName	Duration	StartDate	EndDate	Prededessor
----------------------------------------------------------------------------------
1	Building Contract	3	2019-03-11	2019-03-13	NULL
2	Land Survey	2	2019-03-14	2019-03-15	1
3	Soil Testing	3	2019-03-16	2019-03-18	2
4	Excavation	5	2019-03-19	2019-03-23	3
5	Drilling	4	2019-03-11	2019-03-14	NULL
*/```

Which looks more in line with what you posted as sample output in beginning

Only thing I left out was two columns Predecessor and ActID inside derived table which I added it now

Can you explain this?

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Thursday, March 14, 2019 6:39 AM
• ```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,2)
INSERT INTO @Test VALUES(4,'Land Excavation',5,3)
INSERT INTO @Test VALUES(5,'Land Drilling',4,NULL)

DECLARE @Test1 AS TABLE(ActID INT,ActName VARCHAR(50),Duration INT,Predecessor INT,StartDate DATE,EndDate DATE)

DECLARE @Counter INT,@startdate DATE, @startdateentered DATE
SET @Counter = 1
SET @startdate = '2019-03-11'
SET @startdateentered = @startdate

WHILE (@Counter <= (SELECT COUNT(*) FROM @Test))
BEGIN
DECLARE @ActID INT,@ActName VARCHAR(50),@Duration INT,@Predecessor INT,@StartDate1 DATE,@EndDate1 DATE

SELECT @ActID = ActID,@ActName = ActName,@Duration = Duration,@Predecessor = Predecessor
FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) Row_Num,ActID,ActName,Duration,Predecessor FROM @Test) t
WHERE t.Row_Num = @Counter

IF(ISNULL(@Predecessor,'0') > 0)
BEGIN
SET @StartDate1 = @startdate
END
ELSE
BEGIN
SET @StartDate1 = @startdateentered
END
DECLARE @NumOfSundays INT
SELECT @NumOfSundays = DATEDIFF(WW, @StartDate1, @EndDate1)

INSERT INTO @Test1 (ActID,ActName,Duration,Predecessor,StartDate,EndDate)
SET @Counter = @Counter + 1
CONTINUE;
END

SELECT * FROM @Test1```

Thursday, March 14, 2019 8:05 AM

### All replies

• There is a mistake on Activity # 1, it will not have any predecessor. So please don't take 2 predecessor for Building contract.
Wednesday, March 13, 2019 9:24 AM
• this you mean?

```DECLARE @startdate date = '20190311'

UPDATE t
SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
END
FROM
(
SELECT StartDate,EndDate,Duration,SUM(Duration) OVER (ORDER BY ActID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CumDur
FROM TableName
)t```

If version is below 2012 you can do like this

```DECLARE @startdate date = '20190311'

UPDATE t
SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
END
FROM
(
SELECT StartDate,EndDate,Duration,COALESCE(CumDur,0) AS CumDur
FROM TableName r
OUTER APPLY
(
SELECT SUM(Duration) AS CumDur
FROM tablename
WHERE ActID < r.ActID
)u
)t```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Wednesday, March 13, 2019 9:24 AM
• First of all thanks for your solution.

Second, it is generating wrong dates, as it is summing the duration which is not required.

Here's what i have written :

```DECLARE @startdate date = '2019-03-11';

declare @Task table (ActID int, ActivityName nvarchar(100),Duration int, StartDate date, EndDate date,Prededessor int)
select 1, 'Building Contract', 3, null,null,null
union all
select 2, 'Land Survey', 2, null,null,1
union all
select 3, 'Soil Testing', 3, null,null,2
union all
select 4, 'Excavation', 5, null,null,3
union all
select 5, 'Drilling', 4, null,null,null

SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
END,

EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,t.Duration - 1,@Startdate)
END

FROM
(
SELECT StartDate,EndDate,Duration,SUM(Duration) OVER (ORDER BY ActID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CumDur
)t

Wednesday, March 13, 2019 10:46 AM

It seems that you have solved your own problem. In order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

Best regards,

Dedmon Dai

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Thursday, March 14, 2019 6:08 AM
• First of all thanks for your solution.

Second, it is generating wrong dates, as it is summing the duration which is not required.

Here's what i have written :

```DECLARE @startdate date = '2019-03-11';

declare @Task table (ActID int, ActivityName nvarchar(100),Duration int, StartDate date, EndDate date,Prededessor int)
select 1, 'Building Contract', 3, null,null,null
union all
select 2, 'Land Survey', 2, null,null,1
union all
select 3, 'Soil Testing', 3, null,null,2
union all
select 4, 'Excavation', 5, null,null,3
union all
select 5, 'Drilling', 4, null,null,null

SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
END,

EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,t.Duration - 1,@Startdate)
END

FROM
(
SELECT StartDate,EndDate,Duration,SUM(Duration) OVER (ORDER BY ActID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CumDur
)t

This is what I get as result using code above

```ActID	ActivityName	Duration	StartDate	EndDate	Prededessor
-------------------------------------------------------------------------------------
1	Building Contract	3	2019-03-11	2019-03-13	NULL
2	Land Survey	2	2019-03-24	2019-03-27	1
3	Soil Testing	3	NULL	NULL	2
4	Excavation	5	2019-03-24	2019-03-27	3
5	Drilling	4	2019-03-11	2019-03-13	NULL```

Can you explain how its correct as per your initial rule?

How did startdate become 2019-03-24 for row with ActID 2? Also if duration is 2 how can end date be 2019-03-27?

It doesnt match the result you were showing in the initial post :)

On the other hand my suggestion looks like this

```DECLARE @startdate date = '2019-03-11';

declare @Task1 table (ActID int, ActivityName nvarchar(100),Duration int, StartDate date, EndDate date,Prededessor int)
select 1, 'Building Contract', 3, null,null,null
union all
select 2, 'Land Survey', 2, null,null,1
union all
select 3, 'Soil Testing', 3, null,null,2
union all
select 4, 'Excavation', 5, null,null,3
union all
select 5, 'Drilling', 4, null,null,null

--My suggestion
UPDATE t
SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
END
FROM
(
SELECT Prededessor,ActID,StartDate,EndDate,Duration,SUM(Duration) OVER (ORDER BY ActID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CumDur
)t

/*

ActID	ActivityName	Duration	StartDate	EndDate	Prededessor
----------------------------------------------------------------------------------
1	Building Contract	3	2019-03-11	2019-03-13	NULL
2	Land Survey	2	2019-03-14	2019-03-15	1
3	Soil Testing	3	2019-03-16	2019-03-18	2
4	Excavation	5	2019-03-19	2019-03-23	3
5	Drilling	4	2019-03-11	2019-03-14	NULL
*/```

Which looks more in line with what you posted as sample output in beginning

Only thing I left out was two columns Predecessor and ActID inside derived table which I added it now

Can you explain this?

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Thursday, March 14, 2019 6:39 AM
• ```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,2)
INSERT INTO @Test VALUES(4,'Land Excavation',5,3)
INSERT INTO @Test VALUES(5,'Land Drilling',4,NULL)

DECLARE @Test1 AS TABLE(ActID INT,ActName VARCHAR(50),Duration INT,Predecessor INT,StartDate DATE,EndDate DATE)

DECLARE @Counter INT,@startdate DATE, @startdateentered DATE
SET @Counter = 1
SET @startdate = '2019-03-11'
SET @startdateentered = @startdate

WHILE (@Counter <= (SELECT COUNT(*) FROM @Test))
BEGIN
DECLARE @ActID INT,@ActName VARCHAR(50),@Duration INT,@Predecessor INT,@StartDate1 DATE,@EndDate1 DATE

SELECT @ActID = ActID,@ActName = ActName,@Duration = Duration,@Predecessor = Predecessor
FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) Row_Num,ActID,ActName,Duration,Predecessor FROM @Test) t
WHERE t.Row_Num = @Counter

IF(ISNULL(@Predecessor,'0') > 0)
BEGIN
SET @StartDate1 = @startdate
END
ELSE
BEGIN
SET @StartDate1 = @startdateentered
END
DECLARE @NumOfSundays INT
SELECT @NumOfSundays = DATEDIFF(WW, @StartDate1, @EndDate1)

INSERT INTO @Test1 (ActID,ActName,Duration,Predecessor,StartDate,EndDate)