Recursive CTE/Bill of Materials question -- how to get only lowest level?

  • pietlinden

    SSC Guru

    Points: 62607

    I'm trying to sort out someone's Bill of Materials process that's happening half in Access VBA and half in SQL Server, so if I sound confused, well, I am.

    I'm trying to return a "shopping list" of all the components necessary to build everything in the work order. (If you use the AdventureWorks2017 database as a model, just think of the SalesOrderDetail table as a starting point … the salient columns are (SalesOrderID, ProductID, OrderQty)

    The way I thought to solve it was to use the Bill of Materials CTE, but join that to this:

    SELECT sod.ProductID, sod.OrderQty
    FROM Sales.SalesOrderDetail sod
    WHERE sod.SalesOrderID = @SalesOrderID

    and end up with a "shopping list" of (SubComponentID, SubcomponentQty * sod.OrderQty) for the whole order.

    So my question is - in the cteBOM query in the database, they're passing the AssemblyID (end Product ID) into the CTE... Is that what I want to do? And then join that CTE result to my "SalesOrderDetail" table (on ProductID)? The other part I find confusing about that CTE is which of the items returned are at the lowest level (have no subassemblies)?

    Feel free to point me to any "Recursive CTEs for Dummies" articles. I won't be offended.

    Thanks!

  • pietlinden

    SSC Guru

    Points: 62607

    Here's my code so far... How do I just return the lowest level components? I feel like I'm missing a join or something somewhere...

    Here's my code so far... How do I just return the lowest level components? I feel like I'm missing a join or something somewhere...
    DECLARE @SalesOrderID INT = 43660;
    WITH cte_BOM (ProductID, Name, Color, Quantity, ProductLevel, ProductAssemblyID, Sort)
    AS  (SELECT P.ProductID,
                CAST (P.Name AS VARCHAR (100)),
                P.Color,
                CAST (1 AS DECIMAL (8, 2)),
                1,
                NULL,
                CAST (P.Name AS VARCHAR (100))
         FROM   Production.Product AS P
                INNER JOIN
                Production.BillOfMaterials AS BOM
                ON BOM.ComponentID = P.ProductID
                AND BOM.ProductAssemblyID IS NULL
                AND (BOM.EndDate IS NULL
                    OR BOM.EndDate > GETDATE())
         UNION ALL
         SELECT P.ProductID,
                CAST (REPLICATE('|---', cte_BOM.ProductLevel) + P.Name AS VARCHAR (100)),
                P.Color,
                BOM.PerAssemblyQty,
                cte_BOM.ProductLevel + 1,
                cte_BOM.ProductID,
                CAST (cte_BOM.Sort + '\' + p.Name AS VARCHAR (100))
         FROM   cte_BOM
                INNER JOIN Production.BillOfMaterials AS BOM
                ON BOM.ProductAssemblyID = cte_BOM.ProductID
                INNER JOIN Production.Product AS P
                ON BOM.ComponentID = P.ProductID
                AND (BOM.EndDate IS NULL
                    OR BOM.EndDate > GETDATE())
        )
    SELECT   sod.ProductID,
             Name,
             Color,
             Quantity,
             ProductLevel,
             ProductAssemblyID,
             Sort
    FROM     cte_BOM INNER JOIN Sales.SalesOrderDetail sod
       ON cte_BOM.ProductAssemblyID = sod.ProductID
    WHERE ProductLevel > 1
    AND sod.SalesOrderID = @SalesOrderID
    ORDER BY Sort;

    One last thing... if there are items in my SalesOrderDetail table that have some of the same components, would I not have a simple totals query at the end, where I group by ComponentID, ComponentDescription and return the total units required, like SUM(subcomponentQty)?

    (I watched Joe Sack's video on Pluralsight, hoping he covered this topic, because he explains things really carefully, but no joy. DRAT!)

    Thanks!

    Pieter

  • pietlinden

    SSC Guru

    Points: 62607

    FWIW, here's my current code... I *think* it works, but maybe it's just AdventureWorks playing tricks on me.

    CREATE PROC PartsSheetForSalesOrder
     @SalesOrderID INT
    AS
    WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS 

        SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, 
       b.EndDate, 0 AS ComponentLevel 
      FROM Production.BillOfMaterials AS b 
      WHERE   b.EndDate IS NULL 
        UNION ALL 
        SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, 
       bom.EndDate, ComponentLevel + 1 
      FROM Production.BillOfMaterials AS bom  
            INNER JOIN Parts AS p 
            ON bom.ProductAssemblyID = p.ComponentID 
            AND bom.EndDate IS NULL 

    SELECT ComponentID, Name, SUM(PerAssemblyQty * sod.OrderQty) AS TotalQty
    FROM Parts AS p 
        INNER JOIN Production.Product AS pr 
      ON p.ComponentID = pr.ProductID
     INNER JOIN Sales.SalesOrderDetail sod
     ON sod.ProductID = pr.ProductID
    WHERE sod.SalesOrderID = @SalesOrderID
    GROUP BY ComponentID, Name
    ORDER BY ComponentID, Name;

    I think I could leave the EndDate stuff out, but since I found the example here, I just left it in.

  • pietlinden

    SSC Guru

    Points: 62607

    The part I finally figured out that threw me at first is that you filter what the CTE returns by adding a WHERE clause outside the CTE (seems to painfully obvious now!). I was reading various examples, and some had filters inside the CTE... which was odd. I watched Steve Stedman's video on Common Table Expressions (on youtube). It's 45 minutes long, but really helpful. Highly recommended, because he breaks down how CTEs work, and what recursion is, and why you need to know. Made the stuff make a LOT more sense.

    (yeah, this is my journal about learning how to write a CTE and use one. LOL)

  • jcelko212 32090

    SSCrazy Eights

    Points: 8863

    I would strongly recommend that you don't take this approach SQL is a declarative language in this kind of recursion is based on tree traversals and procedural code. After 30+ years of doing this sort of stuff, I found the nested set model is the best approach. You can Google that technique and find any number of articles on how to do it. If you need more help, I have a chapter in my book on treason hierarchies and SQL. Without explaining it this is what the query that gives you the leaf nodes of the tree structure in the nested set model looks like:

    SELECT B.*

    FROM Bill_of_Materials AS B

    WHERE B.lft + 1 = B.rgt;

    Doesn't that look a lot easier than your recursion? Don't you think it'll run an order of magnitude or more faster?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • pietlinden

    SSC Guru

    Points: 62607

    Live and learn, I guess.

    I might have to revisit this and rewrite it when I learn how to do it better. I think part of the challenge is that it's really hard to find an example of something when you don't know the name of that something. So I'll check it out.

    I hope my solution is better than the person's who was asking for help - using Access and recordsets/cursors and shuffling data back and forth between SQL Server and Access. My code isn't great, but I hope it's a step in the right direction. =)

    I guess nested sets is the next thing to learn?

  • aaron.reese

    SSChampion

    Points: 13407

    But that will give you all of the nodes and their position in the tree.  The OP specifically asked for only the bottom nodes.  Some of these will be at level 2, some might be at level 7 or 77

  • pietlinden

    SSC Guru

    Points: 62607

    Aaron,

    is there an easy way to identify those?

    Pieter

  • aaron.reese

    SSChampion

    Points: 13407

    not one that I would call easy 🙂

    in the past, I have used STUFF()...FOR XML to build a hierarchical map string (not strictly necessary but helps with debugging) and then taken the last element in the string to get the list of bottom level elements.

    I would be sorely tempted to do this with a DO..WHILE loop and build out a #temp_table (writing to disk is generally faster than in-memory processing and you can index it)

    Take the top level components and build out a dataset of their sub-components that are not already in the temp table.

    Insert the calculated record set into the temp table along with a level reference and their original component

    run the query again and if you get more records back, insert them into the temp table. Repeat until the results set is empty.

    Now take the results set and find the MAX(component_level) for each top level component and link it back to its level component record.  You might find the same sub-components on multiple top level items as a different sub-level.

    Now take these bottom level components and SUM(qty) to find your shopping list

     

     

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply