Remove From My Forums

Calculating Start and End Dates From Predecessors in SQL

Question
0

Sign in to vote

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

Answers

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)
insert @Task 
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

UPDATE @Task
SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
ELSE DATEADD(dd,CumDur,@StartDate)
END,

EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,t.Duration - 1,@Startdate)
ELSE DATEADD(dd,(CumDur + 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
FROM @Task
)t

select * from @Task

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)
insert @Task1 
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
ELSE DATEADD(dd,CumDur,@StartDate)
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
ELSE DATEADD(dd,(CumDur + 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
FROM @Task1
)t

select * from @Task1



/*

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?

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Marked as answer by EmadKhanSpiderGrey Thursday, March 14, 2019 8:05 AM

Thursday, March 14, 2019 6:39 AM

Answerer

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
            SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate)
        END
    ELSE
        BEGIN
            SET @StartDate1 = @startdateentered
            SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate1)
        END
    DECLARE @NumOfSundays INT
    SELECT @NumOfSundays = DATEDIFF(WW, @StartDate1, @EndDate1)
  
    INSERT INTO @Test1 (ActID,ActName,Duration,Predecessor,StartDate,EndDate)
    VALUES (@ActID,@ActName,@Duration,@Predecessor,@StartDate1,DATEADD(DAY,@NumOfSundays,@EndDate1))
    SET @startdate = DATEADD(DAY,1,DATEADD(DAY,@NumOfSundays,@EndDate1))
    SET @Counter = @Counter + 1
    CONTINUE;
END
  
SELECT * FROM @Test1

Marked as answer by EmadKhanSpiderGrey Thursday, March 14, 2019 8:05 AM

Thursday, March 14, 2019 8:05 AM

All replies

0

Sign in to vote

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
ELSE DATEADD(dd,CumDur,@StartDate)
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
ELSE DATEADD(dd,(CumDur + 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
ELSE DATEADD(dd,CumDur,@StartDate)
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
ELSE DATEADD(dd,(CumDur + 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

Edited by Visakh16MVP, Editor Wednesday, March 13, 2019 10:12 AM

Wednesday, March 13, 2019 9:24 AM

Answerer

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)
insert @Task 
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

UPDATE @Task
SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
ELSE DATEADD(dd,CumDur,@StartDate)
END,

EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,t.Duration - 1,@Startdate)
ELSE DATEADD(dd,(CumDur + 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
FROM @Task
)t

select * from @Task

Edited by EmadKhanSpiderGrey Wednesday, March 13, 2019 10:46 AM
Proposed as answer by Dedmon DaiMicrosoft contingent staff Thursday, March 14, 2019 6:06 AM

Wednesday, March 13, 2019 10:46 AM

0

Sign in to vote

Hi EmadKhanSpiderGrey,

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)
insert @Task 
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

UPDATE @Task
SET StartDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN @Startdate
ELSE DATEADD(dd,CumDur,@StartDate)
END,

EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,t.Duration - 1,@Startdate)
ELSE DATEADD(dd,(CumDur + 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
FROM @Task
)t

select * from @Task

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)
insert @Task1 
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
ELSE DATEADD(dd,CumDur,@StartDate)
END,
EndDate = CASE WHEN Prededessor IS  NULL OR ActID = 1 THEN DATEADD(dd,Duration - 1,@Startdate)
ELSE DATEADD(dd,(CumDur + 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
FROM @Task1
)t

select * from @Task1



/*

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?

Marked as answer by EmadKhanSpiderGrey Thursday, March 14, 2019 8:05 AM

Thursday, March 14, 2019 6:39 AM

Answerer

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
            SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate)
        END
    ELSE
        BEGIN
            SET @StartDate1 = @startdateentered
            SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate1)
        END
    DECLARE @NumOfSundays INT
    SELECT @NumOfSundays = DATEDIFF(WW, @StartDate1, @EndDate1)
  
    INSERT INTO @Test1 (ActID,ActName,Duration,Predecessor,StartDate,EndDate)
    VALUES (@ActID,@ActName,@Duration,@Predecessor,@StartDate1,DATEADD(DAY,@NumOfSundays,@EndDate1))
    SET @startdate = DATEADD(DAY,1,DATEADD(DAY,@NumOfSundays,@EndDate1))
    SET @Counter = @Counter + 1
    CONTINUE;
END
  
SELECT * FROM @Test1

Marked as answer by EmadKhanSpiderGrey Thursday, March 14, 2019 8:05 AM

Thursday, March 14, 2019 8:05 AM

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Answered by:

Calculating Start and End Dates From Predecessors in SQL

Question

Answers

All replies