SQL recursive assembly bom cost calculation

Question

Hello everyone,

I have attached table structure, where I want to roll up costs for an assembly starting at lower levels.
Attached image shows just one example where part P2 comes under part P1 and needs to be calculated first for cost and then rolled up to P1.
P2 also shows at Level 1 since it is prime assembly.
I strongly believe this requires a recursive query. Since I am new to recursive, I couldn't find the required output.
Request to please help in finding a solution. Any pointers/ideas will help.
Thanks in advance. Let me know if you need more details.

Answer 1

Check an adjusted query for new details:

declare @temp table ( parent varchar(max), part varchar(max), level int, cost money, qty int, top_nod varchar(max))

insert @temp values
('P1','P2',1,1,1,'P1'),
('P1','S1',1,1,6,'P1'),
('P1','P3',1,3,12,'P1'),
('P2','S2',2,1,5,'P1'),
('P2','S3',2,2,2,'P1'),
('P2','P4',2,3,4,'P1'),
('P4','S2',3,10,2,'P1'),
('P4','S3',3,20,3,'P1'),
('P4','P5',3,30,4,'P1'),
('A1','Q2',1,5,2,'A1'),
('A1','S1',1,20,4,'A1'),
('A1','S2',1,15,5,'A1'),
('Q2','R1',2,10,7,'A1'),
('Q2','S3',2,3,1,'A1'),
('R1','S4',3,4,2,'A1'),
('R1','S5',3,5,5,'A1'),
('R1','S6',3,7,2,'A1')

select * from @temp

;
with Q as
(
    select parent, part, level, cost, qty, top_nod
    from @temp
    union all
    select t.parent, Q.part, t.level, Q.cost, Q.qty * t.qty, Q.top_nod
    from @temp t
    inner join Q on Q.parent = t.part and Q.level = t.level + 1 and Q.top_nod = t.top_nod
)
select parent, sum(cost * qty) as final_cost
from Q 
where parent = top_nod
group by parent
order by parent

Results:

/*
parent   final_cost
A1         969,00
P1         864,00
*/

Answer 2

declare @temp table ( parent varchar(max), part varchar(max), level int, cost int, qty int, parentnode varchar(max))

insert @temp values
('P1','P2',1,1,1,'P1'),
('P1','S1',1,1,6,'P1'),
('P1','P3',1,3,12,'P1'),
('P2','S2',2,1,5,'P1'),
('P2','S3',2,2,2,'P1'),
('P2','P4',2,3,4,'P1'),
('P4','S2',3,10,2,'P1'),
('P4','S3',3,20,3,'P1'),
('P4','P5',3,30,4,'P1')

declare @parent varchar(max) = 'P1'
declare @part varchar(max) = 'P2'

;
with Q as
(
select
t1.parent,
t1.part,
t1.level,
t1.parentnode,
t1.cost,
t1.qty,
t1.cost * t1.qty as p
from @temp t1
left join @temp t2
on t2.parent = t1.part
and t2.level = t1.level + 1
and t2.parentnode=t1.parentnode
where t2.part is null
union all
select
t1.parent,
t1.part,
t1.level,
t1.parentnode,
t1.cost,
t1.qty,
(Q.p * t1.qty) as p
from @temp t1
inner join Q on Q.parent = t1.part
and Q.level = t1.level + 1
and Q.parentnode = t1.parentnode
)

SELECT DISTINCT Q1.parent,Q1.part,Q1.level,
CASE WHEN Q1.p IS NOT NULL
THEN SUM(Q1.p)OVER(PARTITION BY Q1.parent,Q1.part)
ELSE Q1.p END AS cost
FROM Q Q1
ORDER BY Q1.parent,Q1.part

Answer 3

Hi there.

I may be late with my answer, nevertheless, I will share my contribution. it may be be helpful.

When one talks about "cost rollup" that means a bit different thing than what it's called here.

As @Erland Sommarskog and @Tom Phillips pointed out, it can get complicated and the provided input data is unclear in definitions. it is missing to mention if qty is QPA or extended qty. also, structures need to be normalized and cost normally is not stored with BOM itself.

Here is a dramatic simplification of T-SQL code I have in production, to demonstrate this.
I hope I have not made any bug in my effort to simplify it from my production code.
I made up a car example to show how it works. I hope my example is sufficient.

use tempdb  
GO  
BEGIN TRANSACTION  
  
-- trivialized representation  
  
CREATE TABLE dbo.Items -- or parts, sub-assemblies and assemblies, item master  
(  
    itmID int NOT NULL  
    ,itmItemNumber nvarchar(50) NOT NULL  
    --,itmDescription nvarchar(100) NOT NULL  
    --,PrimaryUOMCode nvarchar(5) NOT NULL  
    ,CONSTRAINT PK_Items_itmID PRIMARY KEY CLUSTERED(itmID)  
    ,CONSTRAINT UX_Items_itmItemNumber UNIQUE(itmItemNumber)  
)  
  
CREATE TABLE dbo.ItemCosts -- item/part unit costs, also trivialized   
(  
    itmID int NOT NULL  
    ,itmMaterial decimal(9, 4) NOT NULL  
    ,itmLabor decimal(9, 4) NOT NULL  
    ,CONSTRAINT PK_ItemCosts_itmID PRIMARY KEY(itmID)  
    ,CONSTRAINT FK_ItemCosts_Items FOREIGN KEY (itmID) REFERENCES dbo.Items(itmID)  
)  
  
-- Engineering structure  
CREATE TABLE dbo.ItemComponents -- or sub-assembly membership, or parent to parts relations. it defines what and how much goes into each sub-assembly  
(  
    itmID int NOT NULL -- parent  
    ,CompItmID int NOT NULL -- components  
    ,itmcQty decimal(9, 4) NOT NULL -- QPA, quantity per assembly  
    ,CONSTRAINT PK_ItemComponents_itmID_CompItmID PRIMARY KEY CLUSTERED(itmID, CompItmID)  
    ,CONSTRAINT FK_ItemComponents_Items FOREIGN KEY (itmID) REFERENCES dbo.Items(itmID)  
    ,CONSTRAINT FK_ItemComponents_Items_Comp FOREIGN KEY (CompItmID) REFERENCES dbo.Items(itmID)  
)  
  
INSERT INTO dbo.Items  
VALUES  
(1, 'Car')  
,(2, 'Engine')  
,(3, 'Door')  
,(4, 'Body')  
,(5, 'Bolt')  
,(6, 'Nut')  
,(7, 'Crankshaft')  
,(8, 'Piston')  
,(9, 'Block')  
,(12, 'Alternator')  
,(10, 'Rotor')  
,(11, 'Stator')  
,(13, 'Paint')  
,(14, 'Alternator Case')  
,(15, 'Sheet Metal')  
  
INSERT INTO dbo.ItemCosts  
VALUES  
(1, 0, 1) -- 'Car'  
,(2, 0, 1) --  'Engine'  
,(3, 0, 1) --  'Door'  
,(4, 0, 1) --  'Body'  
,(5, 0.1, 0) --  'Bolt'  
,(6, 0.1, 0) --  'Nut'  
,(7, 10, 20) --  'Crankshaft'  
,(8, 5, 5) --  'Piston'  
,(9, 20, 10) --  'Block'  
,(12, 0, 1) --  'Alternator'  
,(10, 1, 1) --  'Rotor'  
,(11, 1, 1) --  'Stator'  
,(15, 2.5, 0) --  'Sheet Metal'  
  
  
INSERT INTO dbo.ItemComponents  
VALUES  
(1, 2, 1)  
,(1, 3, 4)  
,(1, 4, 1)  
  
,(2, 7, 1)  
,(2, 8, 1)  
,(2, 9, 1)  
,(2, 5, 100)  
,(2, 6, 200)  
,(2, 12, 1)  
  
,(12, 10, 1)  
,(12, 11, 1)  
,(12, 5, 8)  
,(12, 6, 8)  
,(12, 14, 1)  
,(1, 5, 122)  
,(1, 6, 222)  
,(4, 13, 5.1234)  
,(3, 13, 2.456)  
,(3, 5, 10)  
,(3, 6, 10)  
  
,(3, 15, 2.5)  
,(4, 15, 10.5)  
  
  
CREATE TABLE dbo.OrderBOMs -- proper BOM tree, expanded into acyclic tree with single root.  
(  
    bomID int NOT NULL -- normally order specific bill ID, for specific customer order that needs manufacturing  
    ,itmID int NOT NULL  
    ,itmIDParent int NULL  
    ,HIDPath hierarchyid NOT NULL -- lineage using itmID  
    ,HID hierarchyid NOT NULL -- proper hierarchy id  
    ,itmcQty decimal(9, 4) NOT NULL -- QPA  
    ,itmcQtyExt decimal(9, 4) NOT NULL -- QPA extended, total per BOM  
    ,CONSTRAINT PK_Items_HID PRIMARY KEY CLUSTERED(bomID, HID)  
    ,CONSTRAINT FK_OrderBOMs_Items FOREIGN KEY (itmID) REFERENCES dbo.Items(itmID)  
    ,CONSTRAINT FK_OrderBOMs_Items_Parent FOREIGN KEY (itmIDParent) REFERENCES dbo.Items(itmID)  
)  
  
DECLARE @itmItemNumber nvarchar(50) = N'Car'  
  
;WITH tree AS  
(  
    SELECT   
        CompItmID = itm.itmID  
        ,itm.itmID  
        ,itmcQty = CAST(1 as decimal(9, 4))  
        ,itmcQtyExt = CAST(1 as decimal(9, 4))  
        ,HIDPath = CAST(CONCAT('/', itm.itmID, '/') as varchar(8000))  
        ,0 as IsCycle  
        ,HID = CAST('/' as varchar(8000))  
    FROM dbo.Items itm  
    WHERE itm.itmItemNumber = @itmItemNumber  
  
    UNION ALL  
    -- get all direct components  
    SELECT  
        ic.CompItmID  
        ,ic.itmID  
        ,ic.itmcQty  
        ,CAST(r.itmcQty * ic.itmcQty as decimal(9,4)) as itmcQtyExt   
        ,CAST(CONCAT(r.HIDPath, ic.CompItmID, '/') as varchar(8000)) as HIDPath  
        ,CASE WHEN r.HIDPath LIKE CONCAT('%/', ic.CompItmID, '/%') THEN 1 ELSE 0 END as IsCycle  
        ,HID = CAST(CONCAT(r.HID, ROW_NUMBER()OVER(ORDER BY @@SPID), '/')  as varchar(8000))  
    FROM dbo.ItemComponents ic  
    JOIN tree r ON ic.itmID = r.CompItmID  
    WHERE r.IsCycle = 0  
)  
INSERT INTO dbo.OrderBOMs  
SELECT  
    bomID = 1  
    ,itmID = t.CompItmID  
    ,itmIDParent = t.itmID  
    ,t.HIDPath  
    ,t.HID  
    ,t.itmcQty  
    ,t.itmcQtyExt  
FROM tree t  
  
-- show our tree indented  
SELECT  
    itmItemNumber = SPACE(bom.HID.GetLevel() * 4) + itm.itmItemNumber  
    ,bom.itmcQtyExt  
    ,bom.itmcQty  
    ,HIDPath = bom.HIDPath.ToString()  
    ,HID = bom.HID.ToString()  
    ,BOMLevel = bom.HID.GetLevel()  
    --,bom.itmID  
    --,bom.itmIDParent  
FROM dbo.OrderBOMs bom  
JOIN dbo.Items itm ON itm.itmID = bom.itmID  
ORDER BY bom.HID  
  
-- cost rollup process  
  
DECLARE @Costs TABLE -- temporary stack for cost rollup  
(  
    bomID int NOT NULL  
    ,itmID int NOT NULL  
    ,IsProcessed bit NOT NULL  
    ,HID hierarchyid NOT NULL  
    ,itmcQty decimal(9,4) NOT NULL  
    ,itmcQtyExt decimal(9,4) NOT NULL  
    ,BOMLevel smallint NOT NULL  
    ,Material decimal(19,4) NOT NULL  
    ,Labor decimal(19,4) NOT NULL  
    PRIMARY KEY CLUSTERED (HID ASC)  
)  
INSERT INTO @Costs  
SELECT  
    bom.bomID  
    ,bom.itmID  
    ,IsProcessed = 0  
    ,bom.HID  
    ,bom.itmcQty  
    ,bom.itmcQtyExt  
    ,BOMLevel = bom.HID.GetLevel()  
    ,Material = ISNULL(c.itmMaterial, 0)  
    ,Labor = ISNULL(c.itmLabor, 0)  
FROM dbo.OrderBOMs bom  
LEFT JOIN dbo.ItemCosts c ON c.itmID = bom.itmID  
  
DECLARE @Level_max smallint = 0, @rows int = 0, @bomID int = 1  
  
-- tag leaf level parts and discover tree depth  
UPDATE t  
SET t.IsProcessed = 1  
    ,@Level_max = CASE WHEN t.BOMLevel > @Level_max THEN t.BOMLevel ELSE @Level_max END -- discover max BOM level while updating  
OUTPUT SPACE(inserted.HID.GetLevel() * 4) + itm.itmItemNumber as itmItemNumber, inserted.HID.ToString() as HID, inserted.Material as [inserted.Material], deleted.Material as [deleted.Material], inserted.Labor as [inserted.Labor], deleted.Labor as [deleted.Labor], CONCAT('Leaf level update: ',  QUOTENAME(inserted.HID.GetLevel())) as [Action]  
FROM @Costs t -- parent set  
LEFT JOIN dbo.Items itm ON itm.itmID = t.itmID -- only needed to show human readable itmItemNumber  
WHERE t.bomID = @bomID  
AND NOT EXISTS -- have no other components, i.e. leaf level items  
(  
    SELECT 1  
    FROM @Costs t_leaf  
    WHERE t_leaf.bomID = t.bomID  
    AND t_leaf.HID.GetAncestor(1) = t.HID  
)   
SET @rows = @@ROWCOUNT  
SET @Level_max -= 1 -- decrement for the above updated level  
  
WHILE @Level_max >= 0 AND @rows > 0  
BEGIN  
  
    UPDATE t -- rollup to the parent component costs. tree needs to be iterated from bottom up. thus "cost rollup".  
    SET t.IsProcessed = 1  
        ,t.Material = ISNULL(s.Material, 0)  
        ,t.Labor   += ISNULL(s.Labor, 0)  
    OUTPUT SPACE(inserted.HID.GetLevel() * 4) + itm.itmItemNumber as itmItemNumber, inserted.HID.ToString() as HID, inserted.Material as [inserted.Material], deleted.Material as [deleted.Material], inserted.Labor as [inserted.Labor], deleted.Labor as [deleted.Labor], 'update for level:' + QUOTENAME(@Level_max) as [Action]  
    FROM @Costs t  
    LEFT JOIN dbo.Items itm ON itm.itmID = t.itmID  
    OUTER APPLY  
    (  
        SELECT   
            SUM(t2.Material * t2.itmcQty ) as Material  
            ,SUM(t2.Labor * t2.itmcQty) as Labor  
        FROM @Costs t2  
        WHERE t2.bomID = t.bomID  
        AND t2.HID.GetAncestor(1) = t.HID  
    ) s   
    WHERE t.IsProcessed = 0  
    AND t.BOMLevel = @Level_max  
    SET @rows = @@ROWCOUNT  
  
    SET @Level_max -= 1  
  
END  
  
SELECT  
    itmItemNumber = SPACE(bom.HID.GetLevel() * 4) + itm.itmItemNumber  
    ,bom.itmcQty  
    ,bom.itmcQtyExt  
    ,HIDPath = bom.HID.ToString()  
    ,uc.itmMaterial  
    ,uc.itmLabor  
    ,c.Material  
    ,c.Labor  
    ,cc.Cost  
    ,TotalCost = CAST(cc.Cost * bom.itmcQtyExt as decimal(9, 4))  
FROM dbo.OrderBOMs bom  
JOIN dbo.Items itm ON itm.itmID = bom.itmID  
LEFT JOIN dbo.ItemCosts uc ON uc.itmID = bom.itmID  
LEFT JOIN @Costs c ON c.bomID = bom.bomID AND c.HID = bom.HID  
CROSS APPLY(SELECT Cost = CAST(c.Material + c.Labor as decimal(9, 4))) cc  
WHERE bom.bomID = @bomID  
ORDER BY bom.HID  
  
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION  

I use hierarchyid here, but parent/child relation can be used too. hierarchyid simplifies it for me. hierarchyid is hidden treasure in SQL Server.

Cost computation can depend on type of company, type of manufacturing, costing methods used, and many other factors.

Just to give you an example, here is an image showing cost rollup results in our ERP system for this unit.

![167531-2022-01-22-22-18-22-unit.jpg][2]

![167440-2022-01-22-22-16-23-costing.jpg][2]

Note how many cost elements are being processed as well as cost runs, which are compared to each other.
Those need to be separated as they hit different GL accounts in the financial system and get computed at various stages of manufacturing processes.
Any time tree node is changed, cost is recomputed on the fly, instantly, in multi-statement table-valued function at the core. I wrote all backend code.

Cost rollup cannot be done with recursive CTE as SQL Server does not support aggregation in iterative part of CTE, and direction needs to be from tree leaves to the top
At least I cannot see how it can be done.

I hope this demonstrates the process and helps in your quest.

Thanks
Vladimir [2]: /api/attachments/167531-2022-01-22-22-18-22-unit.jpg?platform=QnA [2]: /api/attachments/167440-2022-01-22-22-16-23-costing.jpg?platform=QnA