Hi,
I need to recompute quantities in a BOM structure which only guarantees correct quantity values for leaf nodes.
If the above is a bit blurry don't worry, below is a simplified example.
Consider a hierarchical table defining three columns: ID (PK), PID (parent ID in the hierarchy) and Q (quantity - of something, for the current record).
set nocount on
declare @results table(
ID nvarchar(3),
PID nvarchar(3),
Q int,
lvl int,
ord int
)
The ord
column serves only to sort results.
The lvl
column defines the level for the current record in the hierarchy.
For those wondering how these columns are maintained, the @results table is populated in the real world by a function which does all the tricks; for this example they'll be given hardcoded values.
Now, the problem is that correct Q values are guaranteed only for leaf-level nodes in the hierarchy. For the other nodes Q may or may be not correctly defined. My task is to recompute Q values for these nodes.
Sample data:
insert into @results(ord, lvl, ID, PID, Q)
select 1, 0, 'A' as ID, null as PID, null as Q union
select 2, 1, 'B' , 'A' , 15 union
select 3, 1, 'C' , 'A' , 10 union
select 4, 2, 'B1' , 'B' , 6 union
select 5, 2, 'B2' , 'B' , 4 union
select 6, 2, 'C1' , 'C' , 5 union
select 7, 2, 'C2' , 'C' , 3 union
select 8, 3, 'C11', 'C1', 4 union
select 9, 3, 'C12', 'C1', 3
As you may see, the quantities for B and C1 are wrong: they're 15 and 5 but should be 10 and 7:
select * from @results order by ord
Here's the initial data:
ID PID Q lvl ord
---- ---- ----------- ----------- -----------
A NULL NULL 0 1
B A 15 1 2
C A 10 1 3
B1 B 6 2 4
B2 B 4 2 5
C1 C 5 2 6
C2 C 3 2 7
C11 C1 4 3 8
C12 C1 3 3 9
Finally, the question: is there any way to update this table in a set-based manner so all quantities are updated with bottom-up summed quantities of children nodes?
The best I came with can be seen below, but it's not set based:
declare @level int
select @level = max(lvl) from @results
while (@level > 0)
begin
update r set Q = s.SumQ
from @results r inner join (
select PID, sum(Q) as SumQ
from @results
where lvl = @level group by PID
) s on ( r.ID = s.PID )
set @level = @level - 1
end
select * from @results
which gives the correct quantities:
ID PID Q lvl ord
---- ---- ----------- ----------- -----------
A NULL 20 0 1
B A 10 1 2
C A 10 1 3
B1 B 6 2 4
B2 B 4 2 5
C1 C 7 2 6
C2 C 3 2 7
C11 C1 4 3 8
C12 C1 3 3 9
Thanks!