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.
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