Sql-server – Help with CTE Aggregating Children Recursively

cterecursivesql server

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.

enter image description here

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

Best Answer

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

Related Question