Sql-server – MS SQL 2017: hierarchy tree – get quantity of last level components

ctehierarchysql server

There's a table of items with their components. Components depth vary i.e. some items may have one level of components other may have tens nested components. I need to get quantinty of last level components (have no further childs) e.g.

SELECT ItemId=1, ComponentId=2, Quantity=11 INTO #Items
UNION SELECT ItemId=1, ComponentId=3, Quantity=12
UNION SELECT ItemId=2, ComponentId=3, Quantity=13
UNION SELECT ItemId=2, ComponentId=5, Quantity=14
UNION SELECT ItemId=2, ComponentId=6, Quantity=15
UNION SELECT ItemId=3, ComponentId=4, Quantity=16
UNION SELECT ItemId=3, ComponentId=6, Quantity=17

should return following results:

ComponentId Quantity    
4           2288        (16x13x11)
5           154         (14x11)
6           2431        (17x13x11)

I've tried recursive cte but got far from right results. Any help would be much appreciated.

Regards,
Przemek

Best Answer

You can use a recursive CTE to produce a running total of the quantity field and then simply return the MAX value grouped by ComponentId where the ComponentId is not also in ItemId. This eliminates any components that have child components and shows the highest calculated value from the recursive CTE.

This should work regardless of how many levels of components any particular Item has, it will always return the childless components only.

See here for a working example. Code below.

;WITH CTE AS
(
  SELECT ItemId,
    ComponentId,
    Quantity
  FROM Items
  WHERE ItemID NOT IN (SELECT ComponentId FROM Items)
  UNION ALL
  SELECT b.ItemId,
    b.ComponentId,
    a.Quantity*b.Quantity
  FROM Items b
  INNER JOIN CTE a ON a.ComponentId = b.ItemID
)

SELECT ComponentId,
  MAX(Quantity) AS Quantity
FROM CTE
WHERE ComponentId NOT IN (SELECT ItemId FROM Items)
GROUP BY ComponentId

Results

ComponentId     Quantity
4               2288
5               154
6               2431