Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>1. A way to query the tables to get the aggregated cost.</strong></p> <ol> <li>Calculate the cost for each stage.</li> <li>Use a recursive CTE to get the level for each stage.</li> <li>Store the result in a temp table.</li> <li>Add a couple of indexes to the temp table. </li> <li>Update the cost in the temp table in a loop for each level</li> </ol> <p>The first three steps is combined to one statement. It might be good for performance to do the first calculation, <code>cteCost</code>, to a temp table of it's own and use that temp table in the recursive <code>cteLevel</code>. </p> <pre><code>;with cteCost as ( select s.id, s.fk_parent, isnull(sum(t.cost), 0) as cost from stage as s left outer join task as t on s.id = t.fk_stage group by s.id, s.fk_parent ), cteLevel as ( select cc.id, cc.fk_parent, cc.cost, 1 as lvl from cteCost as cc where cc.fk_parent is null union all select cc.id, cc.fk_parent, cc.cost, lvl+1 from cteCost as cc inner join cteLevel as cl on cc.fk_parent = cl.id ) select * into #task from cteLevel create clustered index IX_id on #task (id) create index IX_lvl on #task (lvl, fk_parent) declare @lvl int select @lvl = max(lvl) from #task while @lvl &gt; 0 begin update T1 set T1.cost = T1.cost + T2.cost from #task as T1 inner join (select fk_parent, sum(cost) as cost from #task where lvl = @lvl group by fk_parent) as T2 on T1.id = T2.fk_parent set @lvl = @lvl - 1 end select id as [Stage ID], cost as [Total Cost] from #task drop table #task </code></pre> <p><strong>2. A trigger on table <code>task</code> that maintains a <code>calculated_cost</code> field in <code>stage</code>.</strong></p> <pre><code>create trigger tr_task on task after insert, update, delete as -- Table to hold the updates declare @T table ( id int not null, cost decimal(18,2) not null default 0 ) -- Get the updates from inserted and deleted tables insert into @T (id, cost) select fk_stage, sum(cost) from ( select fk_stage, cost from inserted union all select fk_stage, -cost from deleted ) as T group by fk_stage declare @id int select @id = min(id) from @T -- For each updated row while @id is not null begin -- Recursive update of stage with cte as ( select s.id, s.fk_parent from stage as s where id = @id union all select s.id, s.fk_parent from stage as s inner join cte as c on s.id = c.fk_parent ) update s set calculated_cost = s.calculated_cost + t.cost from stage as s inner join cte as c on s.id = c.id cross apply (select cost from @T where id = @id) as t -- Get the next id select @id = min(id) from @T where id &gt; @id end </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POOptimizing tree branch data aggregation in SQL Server 2008 (recursion)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMikael Eriksson
    1. USMikael Eriksson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOptimizing tree branch data aggregation in SQL Server 2008 (recursion)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUndeletion
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COWhile waiting for answers, I did solve my problem (I think). I added a few fields, I calculate the `level` of the stage inside a trigger, then run a cursor against all stages, ordered descending by the level, and obtain desired results. All is done inside a transaction which locks all resources, so no leaf in the tree could be modified. It seems to be working, but I need to end the integration part, to get some real data and test it, then I'll post it here too. Your answers seem to be correct, and very interesting to me. Thank you very much for your time.
      singulars
      1. PO
      1. USAlexanderMP
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload