I’m working on determining the total expense for a product based on the raw materials required, while also considering recursive relationships in the production structure. I have two relevant tables:
material_usage: Lists the materials and their respective amounts needed for each product.cost_of_materials: Stores the unit pricing for the raw materials.
Here’s an example query I have written:
-- Define material usage per product
WITH material_usage AS (
SELECT 'A' AS product, 'B' AS material, 0.5 AS quantity
UNION ALL
SELECT 'A', 'C', 0.3
UNION ALL
SELECT 'B', 'Z', 0.5
UNION ALL
SELECT 'B', 'R', 0.3
UNION ALL
SELECT 'Z', 'L', 0.6
),
-- Define raw material costs
cost_of_materials AS (
SELECT 'L' AS material, 123 AS unit_cost
UNION ALL
SELECT 'C', 58
UNION ALL
SELECT 'R', 948
),
-- Recursive CTE to compute the expenses for each product
material_expenses AS (
SELECT
mu.product,
mu.material,
mu.quantity,
cm.unit_cost,
mu.quantity * cm.unit_cost AS expense
FROM
material_usage mu
JOIN
cost_of_materials cm
ON
mu.material = cm.material
UNION ALL
SELECT
mu.product,
mu.material,
mu.quantity,
cm.unit_cost,
mu.quantity * re.expense AS expense
FROM
material_usage mu
JOIN
material_expenses re
ON
mu.material = re.product
)
-- Total expenses calculation per product
SELECT
product,
SUM(expense) AS total_expense
FROM
material_expenses
GROUP BY
product;
Expected Outcome:
For instance, the total expense for A should equal 191.7, derived from:
- 0.5 times the expense of
B - 0.3 times the expense of
C
Where B’s expense is calculated based on its materials (Z, R), and Z relies on L.
Issue:
Unfortunately, the provided query does not yield the correct recursive expenses. I believe the recursive part of the CTE might be incorrect, but I’m unable to identify the exact error. How can I rectify this query to accurately compute the total expenses for products, factoring in their recursive material relationships?