Sql-server – Parent/Child summary rollup

sql serversql-server-2016t-sql

I'm struggling to wrap my head around this one. I'm sure someone will simplify it.

We have a parent/child relationship that I'm needing to roll-up based on sold quantities and sales dollar amounts.

We have a table that stores the parent and their children. Looks like this:

Parent    Component
1000      1300
1000      1301

When an order is placed, our users order a part number of 1000, then add in line items 1300, 1301 as options. Parent parts do not have a sales value assigned to them, but components do have a value. So what I'm trying to do is roll up everything to the parent level (sales and quantities) on a sales order.

Here's how a sales order would look:

SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      0
0001          1300         C              1      500
0001          1301         C              1      350
0001          1301                        1      400

As you can see above, we can have components (StockCode 1301) in here without parents (think of these as replacement parts). If the sales order line does not have a ParentChild value, they need to be excluded from the roll-up.

Here's how I'm wanting this data to look. A parent/child should only have a Qty of the parent.

SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      850
0001          1301                        1      400

I'm guessing that STUFF/FOR XML PATH would be a good starting point?

Here is the test data:

CREATE TABLE #Structure (
  Parent INT
 ,Component INT )
 
 INSERT INTO #Structure (Parent, Component)
 SELECT 1000, 1300
 UNION 
 SELECT 1000, 1301
 
 
 CREATE TABLE #SalesOrder (
   SalesOrder VARCHAR(4)
  ,StockCode INT
  ,ParentChild CHAR(1)
  ,Qty INT
  ,Price INT )

 INSERT INTO #SalesOrder (SalesOrder, StockCode, ParentChild, Qty, Price)
 SELECT '0001', 1000, 'P', 1, 0
 UNION
 SELECT '0001', 1300, 'C', 1, 500
 UNION
 SELECT '0001', 1301, 'C', 1, 350
 UNION
 SELECT '0001', 1301, NULL, 1, 400
 
 SELECT * FROM #Structure

 SELECT * FROM #SalesOrder

 DROP TABLE #SalesOrder
 DROP TABLE #Structure

Best Answer

TLDR: Final query with comments is at the end, but the following answer goes through the steps of building it out.

So what you likely want is a recursive CTE (I'm not sure STUFF can help you with this, at least not easily).

You should have an Item table that is a distinct list of all your assemblies and component item IDs, similar to this example:

Item Table

If I recall correctly from your last question, you have a BOM table that should have a similar structure as this example (with other fields too I'm sure):

BOM Table

A recursive CTE will help you flatten this so all child Components at all levels roll up with the main end item (e.g. Component 2003 rolls up with 2001 into the end item parent 1000).

Here's an example of a recursive CTE:

WITH CTE AS 
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId
FROM CTE

In my example dataset, this produces the following output (every level of every component rolled back up to the original end item):

Recursive CTE Output

Notice end item "1000" on rows 16 through 18. Rows 16 and 17 are "level 1" and row 18 is "level 2" because the component "2004" on row 17 has a component below it, therefor it's the parent on row 18 to component "2005".

Now let's bring in the Component Price column so we can later group by End Item and sum up the total cost like so:

WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId, ComponentPrice
FROM CTE

Component Price Column Added

Finally, if you add a GROUP BY on the End Item column and add a SUM(ComponentPrice) column then you can get the total exploded BOM price of the End Item like so:

WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice
FROM CTE
GROUP BY EndItemId

End Items' Total Prices:

End Items' Total Prices

TLDR:

Your final step would be to join these rolled up End Item prices to your Sales Line table by ItemId to EndItemId like so:

WITH CTE AS -- This is our recursive CTE
(
    SELECT I.ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- These are the fields you'll need for recursion and final grouping later on
    FROM #Item AS I -- Items is your starting point in the recursion (i.e. this defines your base case)
    INNER JOIN #BOM AS B -- Join to the BOMs table to get the child Components
        ON I.ItemId = B.ParentItemId -- Gets all BOM lines with this parent Item

    UNION ALL -- This is how we start to call the CTE recursively

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- Must select the same fields as before for the UNION ALL
    FROM CTE AS C -- This is our recursive case, a CTE referencing itself
    INNER JOIN #BOM AS B -- Joining to the BOM table again to get all child components but notice the difference in join clause
        ON C.ComponentItemId = B.ParentItemId -- This time the previous call's child becomes the parent (i.e. we're joining the BOM table back in where the child component item is the parent item in the BOM table)
),
RolledUpCte AS -- This rolls up the results of the previous recursive CTE by our end assembly item
(
    SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice -- Summing up the price of all the components on the end item
    FROM CTE
    GROUP BY EndItemId -- Group by end item
)

SELECT SL.SalesLineId, SL.ItemId, RUC.EndItemTotalPrice -- Final select of the fields we need from our Sales Line table and our rolled up prices of our end items
FROM RolledUpCte AS RUC
INNER JOIN #SalesLine AS SL
    ON RUC.EndItemId = SL.ItemId

Sales Lines w/ their summed up component prices: Sales Lines w/ their summer up component prices

Your table schema will obviously vary from my example, but this is the concept and example code of what you'd need to do. Instead of ItemIds it looks like you have StockCodes (or whatever your item's identifier is called in your Item and BOM tables).

The only other important thing to be aware of is if you have a BOM that is more than 2 levels deep (e.g. Parent -> Child -> Grandchild -> Great Grandchild) then you'll only want to sum up the lowest level component items otherwise you end up double counting. E.g. if Item A has component B, and component B has components C and D on it, then you want to only sum the prices of C and D for A. If you sum it all then you'll end up double counting by adding B + C + D for A's price. In my past experience we had a column called ItemType that identified which items are the lowest level component items so that when we did the roll up we could add a WHERE ItemType = 'LowestLevelComponent' but it sounds like in your case, all items that have a child don't have a price so then you probably don't have to worry about double counting.

Finding the total quantity of the lowest level components is the same idea too, but instead you'll need a column in the recursive CTE for the ParentQuantity and another one that multiplies the ParentQuantity by the Child's quantity (in the recursive part of the query) like so:

    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, B.ComponentQuantity AS ComponentQuantityMultiplied
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, (B.ComponentQuantity * C.ComponentQuantityMultiplied) AS ComponentQuantityMultiplied
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId

If you have any questions feel free to let me know and I'll explain as best as I can. If this feels like information overload, maybe just start with the TLDR final full query above and just read the comments to try to understand what it does.

Related Question