SQL recursive assembly bom cost calculation

Question

SQL recursive assembly bom cost calculation

Fan, Allan 21

We need to expand a recursive BOM to summarize all raw material costs to check whether the standard cost setting is correct. The BOM structure is as follows：

The expected output is as follows:

Can somebody please help to show how to get the output?
Thanks a lot!

Olaf Helper 47,436 Reputation points

2022-06-24T05:57:38.477+00:00

That's much to less on information.
Just by having a look at your screenshots I don't get the calculation logic for "Total Material Cost", may can you explain it more detailed?

Please post table design as DDL, some sample data as DML statement and the expected result.
Fan, Allan 21 Reputation points

2022-06-24T06:32:39.083+00:00
The requirement is:

show all the items which have BOM, so the A, B and B1 should be in the output

Expand the BOM to lowest level and calculate the cost, the calculation logic is:
B1= 2*B11 + 3*B12 = 2*4 + 3*2 =14
B = 2*B1 + 2* B2 + 2* B3 = 2*14+ 2*2+ 2*3 = 38
A = (1*B + 2*C + 3* D) = (1*38 + 2*2 + 3* 2) = 48
In this case A is finish goods, B & B1 are semi-finish goods, rests are parts or raw materials.
Actual BOM could be up to 6.
Thank you!
Ronen Ariely 15,206 Reputation points

2022-06-24T06:49:41.17+00:00

Hi,

Please avoid using images unless the question is about how to paint. Using queries we need tables to work with.

Please provide:

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).

Regards,
Fan, Allan 21 Reputation points

2022-06-24T07:44:08.107+00:00

Noted, that's first time I raise my hand :-)
Ronen Ariely 15,206 Reputation points

2022-06-26T11:34:15.587+00:00

No problem :-)

We are here to learn, so next time you will ask the question in a better way. At this time I noticed that @LiHong-MSFT provided a demo to work with and a solution.

Accepted answer

0 additional answers

Your answer

Olaf Helper 47,436 Reputation points

2022-06-24T05:57:38.477+00:00

That's much to less on information.
Just by having a look at your screenshots I don't get the calculation logic for "Total Material Cost", may can you explain it more detailed?

Please post table design as DDL, some sample data as DML statement and the expected result.
Fan, Allan 21 Reputation points

2022-06-24T06:32:39.083+00:00

The requirement is:

show all the items which have BOM, so the A, B and B1 should be in the output

Expand the BOM to lowest level and calculate the cost, the calculation logic is:
B1= 2*B11 + 3*B12 = 2*4 + 3*2 =14
B = 2*B1 + 2* B2 + 2* B3 = 2*14+ 2*2+ 2*3 = 38
A = (1*B + 2*C + 3* D) = (1*38 + 2*2 + 3* 2) = 48
In this case A is finish goods, B & B1 are semi-finish goods, rests are parts or raw materials.
Actual BOM could be up to 6.
Thank you!
Ronen Ariely 15,206 Reputation points

2022-06-24T06:49:41.17+00:00

Hi,

Please avoid using images unless the question is about how to paint. Using queries we need tables to work with.

Please provide:

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).

Regards,
Fan, Allan 21 Reputation points

2022-06-24T07:44:08.107+00:00

Noted, that's first time I raise my hand :-)
Ronen Ariely 15,206 Reputation points

2022-06-26T11:34:15.587+00:00

No problem :-)

We are here to learn, so next time you will ask the question in a better way. At this time I noticed that @LiHong-MSFT provided a demo to work with and a solution.

Answer 1

LiHong-MSFT 10,056

Hi @Fan, Allan
Is there a column like 'Parent_Level' in your initial table?
If so, then check this query:

CREATE TABLE #Test (Parent VARCHAR(10),[Level] INT,ParentCost INT,Child VARCHAR(10),Quantity INT,ChildCost INT)  
INSERT INTO #Test VALUES  
('A',1,66,'B',1,52),('A',1,66,'C',2,2),('A',1,66,'D',3,2),  
('B',2,52,'B1',2,18),('B',2,52,'B2',2,2),('B',2,52,'B3',2,3),  
('B1',3,18,'B11',2,4),('B1',3,18,'B12',3,2)  
--SELECT * FROM #Test  
  
;WITH CTE AS  
(  
 SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, t1.ChildCost * t1.Quantity AS total  
 FROM #Test t1 LEFT JOIN #Test t2 ON t2.Parent = t1.Child AND t2.level = t1.level + 1  
 WHERE t2.Child IS NULL  
 UNION ALL  
 SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, (total * t1.Quantity) as total  
 FROM #Test t1 INNER JOIN CTE ON CTE.Parent = t1.Child AND CTE.level = t1.level + 1  
)  
SELECT Parent,ParentCost,SUM(total) AS Total_cost  
FROM CTE  
GROUP BY parent,ParentCost

Output:

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Fan, Allan 21 Reputation points

2022-06-24T07:33:28.203+00:00

Hi @LiHong-MSFT
Unfortunately there's no 'Parent_Level' column in may table, so I cannot use your query.
Anyway thanks for your reply!

LiHong-MSFT 10,056

All right, then check this:

CREATE TABLE #Test (Parent VARCHAR(10),ParentCost INT,Child VARCHAR(10),Quantity INT,ChildCost INT)  
INSERT INTO #Test VALUES  
 ('A',66,'B',1,52),('A',66,'C',2,2),('A',66,'D',3,2),  
 ('B',52,'B1',2,18),('B',52,'B2',2,2),('B',52,'B3',2,3),  
 ('B1',18,'B11',2,4),('B1',18,'B12',3,2)  
   
;WITH CTE_LEVEL AS  
(  
 SELECT Parent,ParentCost,Child,Quantity,ChildCost,1 AS Level  
 FROM #Test  
 WHERE Parent='A'  
 UNION ALL  
 SELECT T.Parent,T.ParentCost,T.Child,T.Quantity,T.ChildCost,C.Level + 1  
 FROM CTE_LEVEL C JOIN #Test T ON C.Child=T.Parent  
),CTE AS  
(  
 SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, t1.ChildCost * t1.Quantity AS total  
 FROM CTE_LEVEL t1 LEFT JOIN CTE_LEVEL t2 ON t2.Parent = t1.Child AND t2.level = t1.level + 1  
 WHERE t2.Child IS NULL  
 UNION ALL  
 SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, (total * t1.Quantity) as total  
 FROM CTE_LEVEL t1 INNER JOIN CTE ON CTE.Parent = t1.Child AND CTE.level = t1.level + 1  
)  
SELECT Parent,ParentCost,SUM(total) AS Total_cost  
FROM CTE  
GROUP BY Parent,ParentCost

Best regards,
LiHong

Fan, Allan 21 Reputation points

2022-06-24T08:45:59.363+00:00

Thank you very much for your quick reply, the output is correct.
But if I change the field type, it doesn't work again: Types don't match between the anchor and the recursive part in column "total" of recursive query "CTE".

DROP TABLE IF EXISTS #Test

CREATE TABLE #Test (Parent VARCHAR(10),ParentCost numeric(18,6),Child VARCHAR(10),Quantity numeric(18,6),ChildCost numeric(18,6))
INSERT INTO #Test VALUES
('A',66,'B',1,52),('A',66,'C',2,2),('A',66,'D',3,2),
('B',52,'B1',2,18),('B',52,'B2',2,2),('B',52,'B3',2,3),
('B1',18,'B11',2,4),('B1',18,'B12',3,2)

;WITH CTE_LEVEL AS
(
SELECT Parent,ParentCost,Child,Quantity,ChildCost,1 AS level
FROM #Test
WHERE Parent='A'
UNION ALL
SELECT T.Parent,T.ParentCost,T.Child,T.Quantity,T.ChildCost,C.level + 1
FROM CTE_LEVEL C JOIN #Test T ON C.Child=T.Parent
),CTE AS
(
SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, t1.ChildCost * t1.Quantity AS total
FROM CTE_LEVEL t1 LEFT JOIN CTE_LEVEL t2 ON t2.Parent = t1.Child AND t2.level = t1.level + 1
WHERE t2.Child IS NULL
UNION ALL
SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, (total * t1.Quantity) as total
FROM CTE_LEVEL t1 INNER JOIN CTE ON CTE.Parent = t1.Child AND CTE.level = t1.level + 1
)
SELECT Parent,ParentCost,SUM(total) AS Total_cost
FROM CTE
group by Parent, ParentCost

LiHong-MSFT 10,056

Hi @Fan, Allan
As the error message says, we need to match the datatypes of the anchor and the recursive part in column "total".
Try this modification:

,CTE AS  
(  
SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, CAST(t1.ChildCost * t1.Quantity AS numeric(18,6) ) AS total  
FROM CTE_LEVEL t1 LEFT JOIN CTE_LEVEL t2 ON t2.Parent = t1.Child AND t2.level = t1.level + 1  
WHERE t2.Child IS NULL  
UNION ALL  
SELECT t1.Parent,t1.ParentCost, t1.Child, t1.level, CAST((total * t1.Quantity)AS numeric(18,6)) as total  
FROM CTE_LEVEL t1 INNER JOIN CTE ON CTE.Parent = t1.Child AND CTE.level = t1.level + 1  
)

Fan, Allan 21 Reputation points

2022-06-26T23:46:20.823+00:00

Hi @LiHong-MSFT
Thanks for your new script, it works perfectly!
And last questin: since in the real production environment, there will be more finish goods, so if we add more finish goods into the BOM list, the output will be incorrect.
For example:
INSERT INTO #Test VALUES
('A',66,'B',1,52),
('A',66,'C',2,2),
('A',66,'D',3,2),
('B',52,'B1',2,18),
('B',52,'B2',2,2),
('B',52,'B3',2,3),
('B1',18,'B11',2,4),
('B1',18,'B12',3,2),
('D',6,'B11',1,4),
('F',6,'B',1,52),
('F',60,'C',2,2)
Is it possibe to also export finish goods 'F' as well?
Thanks a so much!
Fan, Allan 21 Reputation points

2022-06-27T00:55:53.243+00:00

One mistake, should be:
INSERT INTO #Test VALUES
('A',66,'B',1,52),
('A',66,'C',2,2),
('A',66,'D',3,2),
('B',52,'B1',2,18),
('B',52,'B2',2,2),
('B',52,'B3',2,3),
('B1',18,'B11',2,4),
('B1',18,'B12',3,2),
('D',6,'B11',1,4),
('F',60,'B',1,52),
('F',60,'C',2,2)
Fan, Allan 21 Reputation points

2022-06-27T01:31:37.973+00:00

I used a temp table to call this CTE in a loop and resolved this.

Share via

SQL recursive assembly bom cost calculation

0 additional answers

Your answer