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

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

• Thanks Paul for the excellent answer and explanation! Works like a charm. Jul 13, 2018 at 23:27