I am having trouble getting around a sum() in the recursive term. Basically my problem is this.
Lets say 3 different finish products. 'ABC1', 'ABC2', 'ABC3' every one of them is made from 'ABC'. Every 'ABC' is made from 'AB'. Every 'AB' is made from 'A'. I went out and sold 10 of each 'ABC1', 'ABC2', 'ABC3' I am trying to make a query give me a list of each item and how much I need of that item based on how much I have sold.
This is an example of the return that I am looking for
| Item | Level | Sold | On Hand | Required |
|---|---|---|---|---|
| A | 0 | 0 | 0 | 15 |
| AB | 1 | 0 | 10 | 25 |
| ABC | 2 | 10 | 0 | 25 |
| ABC1 | 3 | 10 | 5 | 10 |
| ABC2 | 3 | 10 | 5 | 10 |
| ABC3 | 3 | 10 | 5 | 10 |
For a general table structure you would have
| Item |
|---|
| item_id |
| item_onhand |
AND
| BOM |
|---|
| bom_product_id |
| bom_material_id |
AND
| Sales |
|---|
| sale_id |
| sale_item_id |
| sale_qty |
I cant start at the top and go down in my case. because the dataset takes too long to process. So I have to start with all the sales and work up the tree from there.
My idea was to create a result for each level. And then recursively go up the material tree. Something along the lines of
WITH RECURSIVE sales_req AS(
SELECT item_id,
SUM(sale_qty) AS sales_req_sold,
item_onhand AS sales_req_qoh
FROM sales JOIN item ON sales_item_id = item_id
GROUP BY item_id
UNION
SELECT
item_id,
SUM(sales_req_sold - sales_req_qoh),
item_onhand
FROM
bom
JOIN sales_req ON bom_product_id = sales_req.item_id
JOIN item mat ON bom_material_id = mat.item_id
WHERE sales_req_sold > sales_req_qoh
The first Query Returning Something Like this
| Item | Required |
|---|---|
| ABC | 10 |
| ABC1 | 10 |
| ABC2 | 10 |
| ABC3 | 10 |
And The recursive portion returning something like this
| Item | Required | Notes |
|---|---|---|
| ABC | 15 | ( The sum of sales for "ABC1,ABC2,ABC3" minus the inventory for each one) |
| AB | 25 | ( The sum of ABC requirements from 1,2 and 3 Plus the requirement for the sale of ABC) |
| A | 15 | ( AB Minus the inventory on hand for AB) |
I need some sort of alternate solution to sum function. However there are a few constraints. I have to start with the sales table. I cannot put a limit on the levels. In this example I have 4 levels and only one level has multiple parts on it. But there could be 7 levels and each level could have 3 parts on it. I can assume the top level to be 1 single item.
CodePudding user response：
try this :
WITH RECURSIVE req AS(
SELECT item_id, item_onhand, SUM(sale_qty) AS item_sales
FROM sales INNER JOIN item ON sale_item_id = item_id
GROUP BY item_id, item_onhand
), accum (item_id, item_onhand, item_sales, item_req, level) AS (
SELECT item_id, item_onhand, item_sales, item_sales, 0
FROM req
UNION ALL
SELECT b.bom_product_id, a.item_onhand, a.item_sales, a.item_sales - a.item_onhand, a.level - 1
FROM accum AS a
INNER JOIN bom AS b ON b.bom_material_id = a.item_id
)
SELECT r.item_id, min(a.level) AS level, r.item_onhand AS on_hand, r.item_sales AS sold, sum(item_req) AS required
FROM accum AS a
INNER JOIN req AS r ON r.item_id = a.item_id
GROUP BY r.item_id, r.item_onhand, r.item_sales
ORDER BY level
see test result in https://dbfiddle.uk/J7PMY1fZ[enter link description here]1