Help with CTE Aggregating Children Recursively

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?

SQL Fiddle

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

Unroll the hierarchy into a temporary table first (note the computed column):

CREATE TABLE #Items
(
    Id integer PRIMARY KEY,
    MPath varchar(255) NOT NULL,
    DisplayText varchar(30) NOT NULL,
    OnHandQty integer NOT NULL,
    [Level] integer NOT NULL,
    ParentId integer NOT NULL,
    MaxCanMake integer NULL,
    AvailToSell AS OnHandQty + MaxCanMake
);

WITH Items AS
(
    SELECT 
        I.Id, 
        MPath = CONVERT(varchar(255), I.DisplaySeq), 
        I.DisplayText, 
        I.OnHandQty, 
        0 AS [Level], 
        0 AS ParentId
    FROM dbo.Item AS I 
    WHERE I.ParentId IS NULL

    UNION ALL

    SELECT 
        I.Id, 
        CONVERT(varchar(255), Parent.MPath + '.' + CONVERT(varchar(11), I.DisplaySeq)),
        I.DisplayText, 
        I.OnHandQty, 
        Parent.[Level] + 1, 
        I.ParentId
    FROM Items AS Parent
    JOIN dbo.Item AS I WITH (FORCESEEK)
        ON I.ParentId = Parent.Id
)
INSERT #Items
    (Id, MPath, DisplayText, OnHandQty, [Level], ParentId)
SELECT
    I.Id, I.MPath, I.DisplayText, I.OnHandQty, I.[Level], I.ParentId
FROM Items AS I
OPTION (MAXRECURSION 0);

-- Useful index
CREATE INDEX i 
ON #Items (ParentId, AvailToSell);

That gives us:

╔════╦════════╦══════════════════╦═══════════╦═══════╦══════════╦════════════╦═════════════╗
║ Id ║ MPath ║ DisplayText ║ OnHandQty ║ Level ║ ParentId ║ MaxCanMake ║ AvailToSell ║
╠════╬════════╬══════════════════╬═══════════╬═══════╬══════════╬════════════╬═════════════╣
║ 9 ║ 0 ║ Laptop & Bag Kit ║ 3 ║ 0 ║ 0 ║ NULL ║ NULL ║
║ 26 ║ 0.20 ║ Bag ║ 23 ║ 1 ║ 9 ║ NULL ║ NULL ║
║ 8 ║ 0.5 ║ Laptop Kit ║ 11 ║ 1 ║ 9 ║ NULL ║ NULL ║
║ 7 ║ 0.5.10 ║ Laptop ║ 5 ║ 2 ║ 8 ║ NULL ║ NULL ║
║ 6 ║ 0.5.15 ║ Power Supply ║ 4 ║ 2 ║ 8 ║ NULL ║ NULL ║
╚════╩════════╩══════════════════╩═══════════╩═══════╩══════════╩════════════╩═════════════╝

Now compute MaxCanMake per level, starting with the deepest:

DECLARE @Level integer =
(
    SELECT MAX(I.[Level])
    FROM #Items AS I
);

WHILE @Level >= 0
BEGIN
    UPDATE I
    SET I.MaxCanMake = 
        ISNULL
        (
            (
                SELECT TOP (1)
                    I2.AvailToSell
                FROM #Items AS I2
                WHERE I2.ParentId = I.Id
                ORDER BY
                    I2.AvailToSell ASC
            ),
            0
        )
    FROM #Items AS I
    WHERE I.[Level] = @Level;

    SET @Level -= 1;
END;

The computed column in the temporary table automatically reflects the change in AvailToSell.

The final display query is then:

SELECT
    DisplayText = REPLICATE('---', I.[Level]) + I.DisplayText,
    I.OnHandQty,
    I.MaxCanMake,
    I.AvailToSell 
FROM #Items AS I
ORDER BY 
    I.MPath;

╔════════════════════╦═══════════╦════════════╦═════════════╗
║ DisplayText ║ OnHandQty ║ MaxCanMake ║ AvailToSell ║
╠════════════════════╬═══════════╬════════════╬═════════════╣
║ Laptop & Bag Kit ║ 3 ║ 15 ║ 18 ║
║ ---Bag ║ 23 ║ 0 ║ 23 ║
║ ---Laptop Kit ║ 11 ║ 4 ║ 15 ║
║ ------Laptop ║ 5 ║ 0 ║ 5 ║
║ ------Power Supply ║ 4 ║ 0 ║ 4 ║
╚════════════════════╩═══════════╩════════════╩═════════════╝

dbfiddle

StackExchange Database Administrators Q#203910, answer score: 4