I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised of raw materials that can be assembled to form that finished good. So the available to sell is the current available Finished Good (FG) qty plus the minimum of the components that can be made into the FG.
Example:
Say we are selling a laptop kit that is comprised of a laptop and a carrying bag. If we have 2 kits already made, and 6 laptops and 3 carrying bags, our available to sell of these kits is 2 + 3 = 5 total. The carrying bags are the limiting factor in this case. Even though we have 6 laptops, we can only make 3 more kits because of the bags limiting us.
I've gotten this far and the calculations work from the lowest level 2 up to level 1, but level 0 is not correct. So in this case, the calculation for the laptop kit is correct (11 on hand + we can make 4 more = 15 avail to sell). But the top level Laptop & Bag Kit is not correct. The least Avail to Sell of the direct children of the top level (Laptop & Bag Kit) is 15 + 3 of that kit is on hand = 18, not 14.
I'm thinking maybe I need to add a second recursive CTE instead of the left join I have in the final select?
CREATE TABLE Item (
Id INT,
ParentId INT,
DisplaySeq INT,
DisplayText VARCHAR(30),
OnHandQty INT
);
INSERT INTO Item (Id, ParentId, DisplaySeq, DisplayText, OnHandQty) VALUES
(9, NULL, 0, 'Laptop & Bag Kit', 3),
(8, 9, 5, 'Laptop Kit', 11),
(7, 8, 10, 'Laptop', 5),
(6, 8, 15, 'Power Supply', 4),
(26, 9, 20, 'Bag', 23)
;
;WITH items AS (
SELECT
Id
, 0 as ParentId
, Id as RootId
, 0 AS Level
, CAST(DisplaySeq AS VARCHAR(255)) AS Path
, CAST('---' AS varchar(100)) AS LVL
, CAST(DisplayText as VARCHAR(255)) as DisplayText
, OnHandQty
FROM Item
WHERE ParentId IS NULL
UNION ALL
SELECT
child.Id
, child.ParentId
, parent.RootId
, Level + 1
, CAST(parent.Path + '.' + CAST(child.DisplaySeq AS VARCHAR(255)) AS VARCHAR(255)) AS Path
, CAST('---' + parent.LVL AS varchar(100)) AS LVL
, CAST(parent.LVL + child.DisplayText as VARCHAR(255)) as DisplayText
, child.OnHandQty
FROM
Item child
INNER JOIN items parent
ON parent.Id = child.ParentId
)
SELECT
t.Path
, t.RootId
, t.Id
, t.ParentId
, t.Level
, t.DisplayText
, t.OnHandQty
, COALESCE(s.MaxCanMake, t.OnHandQty) as MaxCanMake
, t.OnHandQty + COALESCE(s.MaxCanMake, 0) as AvailToSell
FROM
items t
left join (
Select
ParentId,
MIN(OnHandQty) as MaxCanMake
FROM items
GROUP BY ParentId
) as s
ON t.Id = s.ParentId
ORDER BY t.Path