SQLTeam.com | Weblogs | Forums

Calculating Start and End Dates From Predecessors in SQL


#1

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

Please provide me the solution asap.

Thanks.


#2

There is a mistake on Activity # 1, it will not have any predecessor. So please don't take 2 predecessor for Building contract.


#3

Sundays are excluded. What about potential holidays, are they ignored?

Also, when posting, please post data in a usable format with the picture. Like this:

CREATE TABLE #actions (
    ActID int IDENTITY(1, 1) NOT NULL,
    ActName varchar(100) NOT NULL,
    Duration decimal(7, 2) NULL,
    Predecessor smallint NULL,
    StartDate datetime(0) NULL,
    EndDate datetime(0) NULL
)
INSERT INTO #actions VALUES
(1, 'Building Contract', 2, NULL, NULL, NULL),
(2, 'Land Survey', 2, 1, NULL, NULL),
(3, 'Soil Testing', 3, 2, NULL, NULL),
(4, 'Land Excavation', 5, 3, NULL, NULL),
(5, 'Land Drilling', 4, NULL, NULL, NULL)

#4

Yes they are ignored


#5

Corrected sample data, in case someone else has time to work on this:

CREATE TABLE #actions (
    ActID int NOT NULL UNIQUE,
    ActName varchar(100) NOT NULL,
    Duration_Mins int NULL,
    Predecessor smallint NULL,
    StartDate datetime NULL,
    EndDate datetime NULL
)
INSERT INTO #actions VALUES
(1, 'Building Contract', 2*60, NULL, NULL, NULL),
(2, 'Land Survey', 2*60, 1, NULL, NULL),
(3, 'Soil Testing', 3*60, 2, NULL, NULL),
(4, 'Land Excavation', 5*60, 3, NULL, NULL),
(5, 'Land Drilling', 4*60, NULL, NULL, NULL)

#6

I'm struggling with this same issue. I'm grossly simplifying this, but consider a table of tasks.

create table #TestTasks (TaskId varchar(8) not null, TaskDescription varchar(40) not null, Duration int not null )

alter table #TestTasks add primary key (TaskId)

insert into #TestTasks (TaskId, TaskDescription, Duration)
values ('00000001', 'Task 1', 3)

insert into #TestTasks (TaskId, TaskDescription, Duration)
values ('00000002', 'Task 2', 6)

insert into #TestTasks (TaskId, TaskDescription, Duration)
values ('00000003', 'Task 3', 1)

insert into #TestTasks (TaskId, TaskDescription, Duration)
values ('00000004', 'Task 4', 7)

insert into #TestTasks (TaskId, TaskDescription, Duration)
values ('00000005', 'Task 5', 9)

declare @BeginDate datetime
set @BeginDate = '20190301'

Given the initial begin date (@BeginDate), and assuming the tasks are executed sequentially by taskid, I should be able to calculate a begin and end date for each task, resulting in something like this:

TaskId TaskDescription Duration BeginDate EndDate
00000001 Task 1 3 3/01/2019 3/04/2019
00000002 Task 2 6 3/04/2019 3/10/2019
00000003 Task 3 1 3/10/2019 3/11/2019
00000004 Task 4 7 3/11/2019 3/18/2019
00000005 Task 5 9 3/18/2019 3/27/2019

So far I've tried LAG and several flavors of CTEs. No bueno. Any help/suggestions/hints/direction will be greatly appreciated. I think my mind is all confusion at this point.

Thanks

Payson


#7

A better example of the result set, I hope. I'm still getting used to this website.

image

Payson


#8

Assumptions : -
Predecessor on ActID 1 should be NULL

IF OBJECT_ID('tempdb..#actions') IS NOT NULL
DROP TABLE #actions

CREATE TABLE #actions (
ActID int NOT NULL,
ActName varchar(100) NOT NULL,
Duration decimal(7, 2) NULL,
Predecessor smallint NULL,
StartDate DATE NULL,
EndDate DATE NULL
)
INSERT INTO #actions VALUES
(1, 'Building Contract', 2, NULL, NULL, NULL),
(2, 'Land Survey', 2, 1, NULL, NULL),
(3, 'Soil Testing', 3, 2, NULL, NULL),
(4, 'Land Excavation', 5, 3, NULL, NULL),
(5, 'Land Drilling', 4, 4, NULL, NULL)

declare @BeginDate Date
set @BeginDate = '20190301';

WITH CTE -- create a list of date from begin date
AS (SELECT @BeginDate AS ADate,
@BeginDate AS FirstDate
UNION ALL
SELECT DATEADD(dd, 1, ADate),
FirstDate
FROM CTE
WHERE ADate < DATEADD(dd, 355, FirstDate)),
MyDatesOfInterest
AS (SELECT ADate -- remove Sundays and Holidays
FROM CTE
WHERE DATEPART(WEEKDAY, ADate) <> 1 -- Sunday
--AND NOT ADate IN (SELECT HolidayDate FROM TableOfHolidays),
),
YourDataWithTwist
AS (SELECT ActName,
Duration,
-- Get Total Duration Days to StartDate
ISNULL(
( SELECT SUM(Duration) + 1
FROM #actions B
WHERE ISNULL(B.ActID, 0) <= ISNULL(A.Predecessor, 0)
),1) AS TotalDaysStart,
-- Get Total Duration Days to EndDate
( SELECT SUM(Duration)
FROM #actions B
WHERE ISNULL(B.ActID, 0) <= ISNULL(A.ActID, 0)
) AS TotalDaysEnd
FROM #actions A)
SELECT Y.ActName,
Y.Duration,
B.ADate,
E.ADate
FROM YourDataWithTwist Y
LEFT JOIN
( SELECT ADate,ROW_NUMBER() OVER (ORDER BY ADate) AS DayCount
FROM MyDatesOfInterest) B ON B.DayCount = Y.TotalDaysStart
LEFT JOIN
( SELECT ADate,ROW_NUMBER() OVER (ORDER BY ADate) AS DayCount
FROM MyDatesOfInterest ) E ON E.DayCount = Y.TotalDaysEnd
OPTION (MAXRECURSION 0);


#9

Wrong query. Not generating correct date


#10

Shouldn't "Task 1" end on 3/3? If you start something on the 1st that takes 3 total days, shouldn't it be done by the 3rd?


#11

Scott

I don’t think so. If you start at 8:00 on the 1st, 3 24 hour days puts you at 8:00 on the 4th.

Lol, I’ve worked too many all nighters to discount that.

Thanks for paying attention

Payson


#12

How about this:

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

with cte
  as (select actid
            ,1 as rn
        from yourtable
       where predecessor is null
      union all
      select a.actid
            ,b.rn+1
        from yourtable as a
             inner join cte as b
                     on b.actid=a.predecessor
     )
select a.actid
      ,b.actname
      ,b.duration
      ,b.predecessor
      ,dateadd(day
              ,sum(b.duration) over(order by (select null)
                                     rows unbounded preceding
                                   )
              -b.duration
              ,@startdate
              )
       as startdate
      ,dateadd(day
              ,sum(b.duration) over(order by (select null)
                                     rows unbounded preceding
                                   )
              ,@startdate
              )
       as enddate
  from cte as a
       inner join yourtable as b
               on b.actid=a.actid
 order by a.rn
;

#13

What dates you expecting?
Should they exclude Sundays? (The example code excludes Sunday)
Can you provide your expected outcome?
What is 1 duration? One day?


#14

Yes, 1 Day duration means 1 day and sunday should be excluded


#15

Your calculation is wrong, you are calculating the dates from previous activity ID not from there predecessors.


#16

This should work (at least it works for the supplied sample data):

Declare @testTasks Table (
        TaskId varchar(8) Not Null
      , TaskDescription varchar(40) Not Null
      , Duration int Not Null
        );

 Insert Into @testTasks (TaskId, TaskDescription, Duration)
 Values ('00000001', 'Task 1', 3)
      , ('00000002', 'Task 2', 6)
      , ('00000003', 'Task 3', 1)
      , ('00000004', 'Task 4', 7)
      , ('00000005', 'Task 5', 9);

Declare @beginDate date = '2019-03-01';

   With endDates
     As (
 Select tt.TaskId
      , tt.TaskDescription
      , tt.Duration
      , EndDate = dateadd(day, sum(tt.Duration) over(Order By tt.TaskId), @beginDate)
   From @testTasks      tt
        )
 Select ed.TaskId
      , ed.TaskDescription
      , ed.Duration
      , StartDate = dateadd(day, -Duration, EndDate)
      , ed.EndDate
   From endDates        ed
  Order By
        TaskId;

The Duration should be used to calculate the End Date - then it is a simple calculation to get the start date from the derived end date.


#17

Since you give us next to nothing to work with, this is what you should expect.

I'm risking another guess at, what I have understood, you are trying to attempt (hmm you actually didn't show us your attempt, so this is just me guessing ... again):

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

with cte_dates
  as (select top(1) with ties
             a.dt
            ,-1+row_number() over(order by a.dt) as daynum
        from (select top(1) with ties
                     dateadd(day,-1+row_number() over(order by (select null)),@startdate) as dt
                    ,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
                from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally1(n)
                     cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally2(n)
                     cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally3(n)
               order by exclude
             ) as a
             cross apply (select sum(duration) as duration
                            from yourtable
                         ) as b
       order by case when row_number() over(order by a.dt)<=b.duration then 0 else 1 end
     )
    ,cte_sequence
  as (select actid
            ,1 as rn
        from yourtable
       where predecessor is null
      union all
      select a.actid
            ,b.rn+1
        from yourtable as a
             inner join cte_sequence as b
                     on b.actid=a.predecessor
     )
    ,cte_dateno
  as (select a.actid
            ,b.actname
            ,b.duration
            ,b.predecessor
            ,sum(b.duration) over(order by a.rn
                                   rows unbounded preceding
                                 )
            -b.duration
             as dayfrom
            ,sum(b.duration) over(order by a.rn
                                   rows unbounded preceding
                                 )
             as dayto
        from cte_sequence as a
             inner join yourtable as b
                     on b.actid=a.actid
     )
select a.actid
      ,a.actname
      ,a.duration
      ,a.predecessor
      ,min(b.dt) as startdate
      ,max(b.dt) as enddate
  from cte_dateno as a
       inner join cte_dates as b
               on b.daynum>=a.dayfrom
              and b.daynum<a.dayto
 group by a.actid
         ,a.actname
         ,a.duration
         ,a.predecessor
 order by startdate
;

#18

Data setup first, main code follows:

IF OBJECT_ID('tempdb.dbo.#actions') IS NOT NULL
    DROP TABLE #actions;
GO
CREATE TABLE #actions (
    ActID int NOT NULL,
    ActName varchar(100) NOT NULL,
    Duration decimal(7, 2) NULL,
    Predecessor smallint NULL,
    StartDate datetime NULL,
    EndDate datetime NULL
)

TRUNCATE TABLE #actions
INSERT INTO #actions VALUES
(1, 'Building Contract', 2, NULL, NULL, NULL),
(2, 'Land Survey', 2, 1, NULL, NULL),
(3, 'Soil Testing', 3, 2, NULL, NULL),
(4, 'Land Excavation', 5, 3, NULL, NULL),
/*Added: I wanted to allow actions for a single predecessor#, with diff. durations*/
(5, 'Sign Preparation', 3, 3, NULL, NULL),
(6, 'Land Drilling', 4, 4, NULL, NULL)

DECLARE @StartDate date
SET @StartDate = '20190311'

SELECT @StartDate = ISNULL(StartDate, @StartDate)
FROM #actions
WHERE (Predecessor IS NULL OR Predecessor = 0)
ORDER BY StartDate /*Min StartDate if multiple*/
--SELECT @StartDate

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cte_Future_Dates AS (
    SELECT DATEADD(DAY, t.number, @StartDate) AS Date, 
        ROW_NUMBER() OVER(ORDER BY DATEADD(DAY, t.number, @StartDate)) - 1 AS DaysDiff
    FROM cteTally10K t
    WHERE t.number BETWEEN 0 AND 365 * 3 + 1 AND
        DATEDIFF(DAY, 0, DATEADD(DAY, t.number, @StartDate)) % 7 <> 6
    --ORDER BY t.number
),
cte_max_predecessor_durations AS (
    SELECT ISNULL(Predecessor, 0) AS Predecessor, MAX(Duration) AS Duration,
        ROW_NUMBER() OVER(ORDER BY ISNULL(Predecessor, 0)) AS row_num,
        MIN(StartDate) AS StartDate
    FROM #actions
    GROUP BY ISNULL(Predecessor, 0)
),
cte_activities AS (
    SELECT Predecessor, Duration AS Total_Duration, Duration AS Max_Duration, row_num
    FROM cte_max_predecessor_durations
    WHERE row_num = 1
    UNION ALL
    SELECT cmpd.Predecessor, CAST(cmpd.Duration + ca.Total_Duration AS decimal(7, 2)),
        cmpd.Duration, cmpd.row_num
    FROM cte_activities ca
    INNER JOIN cte_max_predecessor_durations cmpd ON cmpd.row_num = ca.row_num + 1    
)
SELECT a.ActID, a.ActName, a.Duration, a.Predecessor, 
    --ca.Total_Duration, 
    cfd_start.Date AS StartDate, cfd_end.Date AS EndDate
FROM #actions a
INNER JOIN cte_activities ca ON ca.Predecessor = ISNULL(a.Predecessor, 0)
INNER JOIN cte_Future_Dates cfd_start ON cfd_start.DaysDiff = ca.Total_Duration - ca.Max_Duration
INNER JOIN cte_Future_Dates cfd_end ON cfd_end.DaysDiff = ca.Total_Duration - 
    (ca.Max_Duration - a.Duration)

#19

You are not calculating the activity start date from its predecessor, you are calculating it from its previous line record which is against standard predecessor law.
For Example: If on row 2 we have defined predecessor 10 then you it should calculate the 10th predecessor activity's start and end date then get back to row 2 and manipulate its start and end date.


#20

You are not calculating the activity start date from its predecessor, you are calculating it from its previous line record which is against standard predecessor law.
For Example: If on row 2 we have defined predecessor 10 then you it should calculate the 10th predecessor activity's start and end date then get back to row 2 and manipulate its start and end date.