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.
There is a mistake on Activity # 1, it will not have any predecessor. So please don't take 2 predecessor for Building contract.
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)Yes they are ignored
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)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
A better example of the result set, I hope. I'm still getting used to this website.
Payson
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);
Wrong query. Not generating correct date
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?
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
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
;
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?
Yes, 1 Day duration means 1 day and sunday should be excluded
Your calculation is wrong, you are calculating the dates from previous activity ID not from there predecessors.
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.
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
;
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)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.
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.