Expand components recursively to get the total cost
TL-TR: I want to get the total value of the last item that is made from others, from which I have the purchase price. The problem lies in the semi-finished products.
Consider three types of elements:
- End Item: It is made from raw and / or semi-finished items. These are the ones I want to get from the total cost.
- Semi-finished Product: It is made from raw items and / or semi-finished products.
- Raw Material: I have a unit cost of them.
The last element can be made, among other things, from a semi-finished product, which can be made, among other things, from another semi-finished product, etc. at the undefined level until you reach all the raw items.
I know how to do this in C #, but I'm interested in a pure SQL solution that I believe is doable.
The SQL Fiddle is provided here:
http://sqlfiddle.com/#!6/138c3
And this is as far as I can get with the request ...
SELECT BOM.output, SUM(P.price * BOM.quantity) as Total
FROM BOM
INNER JOIN
Prices P ON P.input = BOM.Input
GROUP BY BOM.output
But, of course, this does not result in the receipt of semi-finished products in the amount, since they do not exist in the price table.
EDIT: Another try, but it gives an error that grouping is not allowed in recursive queries.
WITH cte_table AS (
SELECT BOM.output, SUM(P.price * BOM.quantity) as Total
FROM BOM
INNER JOIN
Prices P ON P.input = BOM.Input
GROUP BY BOM.output
UNION ALL
SELECT BOM.output, SUM(ISNULL(P.price,T.Total) * BOM.quantity) as Total
FROM BOM
LEFT JOIN
Prices P ON P.input = BOM.Input
LEFT JOIN
cte_table T ON T.output = BOM.Input
GROUP BY BOM.output
)
SELECT *
FROM cte_table
And some example of expected output (in light gray it should be computed, in black it is data):
source to share
You need to use a recursive query:
Request 1 :
with a as(
SELECT BOM.output, BOM.input, P.price * BOM.quantity as price, 1 as level,
convert(varchar(max), BOM.input) as path
FROM BOM
INNER JOIN
Prices P ON P.input = BOM.Input
UNION ALL
SELECT BOM.output, BOM.input, a.price * BOM.quantity as price, a.level + 1 as level,
a.path + '/' + bom.input
FROM a
INNER JOIN BOM ON a.output = BOM.Input)
select output, sum(price) from a
group by output
-- select * from a
Results :
| output | |
|--------|-------------------|
| Item 3 | 64.32000000000001 |
| Semi 1 | 63 |
| Semi 2 | 60.4 |
source to share