December 27, 2015 at 10:08 am
I have a table in which there are rows with start and end dates. Each item has a dependent task where its start date is calculated from. Below is a attached a snip of some of the table rows. I need a statement that will update all the dependent tasks of the whole table when a start date or duration changes. I have this in asp.net using recursive functions but it takes about 45 seconds to update and i need something that does not use the client to recalculate but needs to remain in the database. I have tried recursive stored procs but it exceeds the recursion limit. Any ideas?
December 27, 2015 at 11:12 am
In order to get the best answer you should post the table structure, sample data and what data you expect to get in return, but see below - does this do what you require?
create table #Tasks
(TaskId Int not null constraint Pk_Task_Taskid Primary key
,ItemIdintnot null
,StartOndatetimenull
,Durationintnot null
,Endsdatetimenull
,DepTaskIdintnot null
);
insert into #Tasks
values(104419, 513, '2015-10-01', 1, '2015-10-01', 0)
,(104420, 514, null, 2, null, 104419)
,(104421, 627, null, 1, null, 104419)
,(104422, 515, null, 1, null, 104420)
,(104423, 516, null, 3, null, 104422)
,(104424, 517, null, 1, null, 104423)
,(104425, 589, null, 1, null, 104424)
,(104426, 590, null, 1, null, 104425)
,(104427, 518, null, 1, null, 104426);
with cte as (
selectTaskId
,ItemId
,StartOn
,Duration
,Ends
,DepTaskId
from#Tasks
whereDepTaskId = 0
union all
select
t.TaskId
,t.ItemId
,c.Ends
,t.Duration
,(c.Ends+t.Duration) as Ends
,t.DepTaskId
from#Tasks t
joincte c
ont.DepTaskId =c.TaskId
wheret.DepTaskId <> 0
)
select *
from cte;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply