# Which items can be built (do we have enough product for)?

• pietlinden

SSC Guru

Points: 62682

I have a stupid question. I'm dealing with a Bill Of Materials problem... I can get the bill of materials, but the kicker is I have Invoices with (ProductID, Quantity) and I need to figure out which I have the "ingredients" to manufacture. I know I can get a "table" of current inventory and another of "recipe" X qty... but is there a non-cursor based way of figuring how which Inventory line items I can build?

I know I can do compare the (ProductID, totalQuantity) in the "recipes" to inventory, but how to do process the next order? Use a windowing function and do running totals of inventory? Then update when I assign a BOM full of items to a work order?

Is there a sane way to do this without using a cursor? (Yeah, I know that's a dirty word, but if I do that, I know that I'm allocating parts I still have! I just stop allocating when I don't have enough of an "ingredient" in inventory. I was thinking of writing a query that go the current inventory levels of all the ingredients requested, and then did a running total to allocate ingredient quantities to finished product requests...

Anybody have any pointers on how to do this? The cursor-ish approach isn't my favorite - process one request at a time until there isn't enough product left... But what are my other options?

Thanks!

Pieter

SSChasing Mays

Points: 658

One sane way to allocate inventory is to do it when the invoices are generated.  Is the purpose here to answer the question "how many of which products can I make?" or is it to explicitly allocate the inventory?  Or both?  Is the inventory tracked by unique item or sku?  Do other processes compete for inventory items?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• jcelko212 32090

SSCrazy Eights

Points: 8927

Google around for the term "relational division" as a solution. Many years ago (well maybe decades now) I did this for a mail-order barbecue company (Corky's). Their products are gift baskets of assorted food items. I've covered this example in several of my books.

Look at this old article of mine for an explanation of this programming technique.

https://www.red-gate.com/simple-talk/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

• pietlinden

SSC Guru

Points: 62682

I think I remember your article about "Find all pilots that can fly all the planes" question. (Yep, that's the one!)

Is there a way to process the "build orders" without using a cursor? The reason I ask is because allocating parts to one "build order" means it won't be available to the next build order.  I played around with a windowing function to generate the current inventory level on the fly, but I'm not sure how stable that will be.  Given that there would be a reasonably limited number of daily build orders, I could get away with doing using a cursor... it would at least give me accurate part counts.

I see how the pilots and planes example would fit this, but I don't see how to process the orders without using a cursor. I don't like cursors, but I really don't see any other option here. How would I know/calculate the new running inventory levels AND at the same time figure out if I have enough parts for a given build order? I can do them separately, but I don't see how to do the two together in a query.

Yeah yeah.. I guess that makes me a rookie. Oh well.

Thanks!

Pieter

• Steve Jones - SSC Editor

SSC Guru

Points: 717470

Do you have to look at your orders in some sequence? Meaning, I might need 5 Product3 and 2 Product4 for an order, but also 2 Product3 and 4 Product4 for separate one. I could build either with some level of inventory, but not necessarily both.

Not compelely sure how to solve this, though I think relational division is the right track. Just not sure you can do this in one query unless you are sure you have a sequence for the divisor. Even then, subtracting out the used quantities for order 1 before looking at order 2 might be overly complex in the SQL.

Maybe Drew will chime in. He comes up with some great creative SQL solutions.

• drew.allen

SSC Guru

Points: 76709

Steve Jones - SSC Editor wrote:

Maybe Drew will chime in. He comes up with some great creative SQL solutions.

I would need some data and expected results to work with.

Drew

J. Drew Allen

• jcelko212 32090

SSCrazy Eights

Points: 8927

http://tdan.com/data-mining-on-a-budget/5343

Here's a link to an old article I wrote of my adventures in barbecue land. It's easy enough to find the various shades of relational division pick which one is appropriate for your particular problem.

However, you have to present each basket's content, one at a time. Otherwise, you wind up with a combinatorial explosion and this becomes an NP-complete problem. It's a real bitch to optimize these bin packing problems. The first problem is just defining what optimized means. Do you construct the largest number of completed assemblies from the parts available? You look at a window of (n) desired complete assemblies, and trial possible arrangements of them to see who meets what criteria. It's not easy

• pietlinden

SSC Guru

Points: 62682

Okay, sorry everybody. I found out that the question was way easier than I thought. (That's what happens when you don't get scripts to recreate the tables and data and some expected results.) Turns out, they wanted a the standard recursive CTE to explode a bill of materials... That and a summary query to count up part quantities.

I guess on the plus side, I got to play with CTEs for a while. (I can hear the "How's your tempdb feeling?" already!)

• Steve Jones - SSC Editor

SSC Guru

Points: 717470

If you're interested, writing up your experiences and learning how to do this would make a great beginner article. You may have a perspective other authors don't.

• pietlinden

SSC Guru

Points: 62682

Steve.

I have a question about the rCTE example. In the rCTE, the Product Assembly is "decomposed" into parts, which is then decomposed again (in the rCTE)… but how do you know when you've reached the "bottom", so you know which (and how many) Component part IDs you need to build an item?

I'm using the expanded version of AdventureWorks2016. Here's my code:

`I'm using the expanded version of AdventureWorks2016. Here's my code:use adventureworks2016_ext;goCREATE TABLE #InvoiceList (InvoiceID INT);GO-- you might use INSERT INTO... SELECT... to get a list of invoices to order parts for.-- I'm just cheating a little.INSERT INTO #InvoiceList (InvoiceID) VALUES (43659), (43660), (43661), (43662);WITH RecurThroughBOM AS(    --- this part returns all the lowest-level components and quantities of an assembly    SELECT        BOM.ProductAssemblyID,        BOM.ComponentID,        BOM.PerAssemblyQty,        BOM.BOMLevel    FROM        Production.BillOfMaterials BOM    WHERE        BOM.ProductAssemblyID IS NULL        AND        SYSDATETIME() BETWEEN BOM.StartDate AND ISNULL(BOM.EndDate,'2099-12-31')    UNION ALL    SELECT        BOM.ProductAssemblyID,        BOM.ComponentID,        BOM.PerAssemblyQty,        BOM.BOMLevel    FROM        Production.BillOfMaterials BOM        INNER JOIN RecurThroughBOM CTE        ON BOM.ProductAssemblyID = CTE.ComponentID    WHERE        sysdatetime() BETWEEN BOM.StartDate AND ISNULL(BOM.EndDate,'2099-12-31'))/*    you could join this back to a "table" of Products you want to build    because a CTE is basically a funky view.*/SELECT inv.ComponentID        ,SUM(inv.QuantityNeeded) As TotalNeededFROM(SELECT dtl.SalesOrderID,    dtl.ProductID,    BOM.ComponentID,    QuantityNeeded = dtl.OrderQty * BOM.PerAssemblyQtyFROM     Sales.SalesOrderDetail dtl        INNER JOIN    RecurThroughBOM BOM        ON dtl.productID = BOM.ProductAssemblyID        --- join to your "orders" table or whatever right here        -- filtering out the orders that have been completedINNER JOIN #InvoiceList il ON dtl.SalesOrderID = il.InvoiceID) invGROUP BY inv.ComponentIDORDER BY inv.ComponentID;`

All that to ask one simple question:

Is this returning just the very lowest level components (components that are not sub-assemblies or assemblies)? What is the BOMLevel telling me? whether the item is part of a larger assembly?

thanks!

Pieter

• This reply was modified 5 months, 1 week ago by  pietlinden. Reason: Clarification
• pietlinden

SSC Guru

Points: 62682

This is a "note to self", I guess, so...

Self,

this is as far as I have gotten. it's not quite right maybe, because I have yet to figure out how to return just subassemblies, but this much seems to work (I can join to a table of Orders that should be processed, and expand those in one query.  Here's the query... The #ProcessOrders is just a sort of stub table with a couple of Orders in it so I could test with more than one Order.

`use AdventureWorks2017;GO-- this is setup for the rest of the procedureCREATE TABLE #ProcessOrders (OrderToProcess INT PRIMARY KEY);GOINSERT INTO #ProcessOrders(orderToProcess) VALUES (43659),(43660);--- now I can actually  answer the questionWITH 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   cte_BOM.ProductID,         [Name],         Color,         Quantity * sod.OrderQty AS Total_Qty,         ProductLevel,         ProductAssemblyID,         SortFROM     cte_BOM-- inner join to a list of products & quantitiesINNER JOIN Sales.SalesOrderDetail sod ON sod.ProductID = cte_BOM.ProductAssemblyIDWHERE sod.SalesOrderID IN (SELECT orderToProcess FROM #ProcessOrders)ORDER BY Sort; `

The part I haven't worked out yet is how to select one or more subassemblies and get the parts for those, but I think that's trivial now... just ID the ones I want, and dump into a temp table and join.

Go to bed, Self. It's late.

• pietlinden

SSC Guru

Points: 62682

If I sequence the Work Orders somehow, I could use one of those dreaded cursor things... then I could do something like this:

1. get complete list of components required to fulfill the work order.
2.  check to see if there are enough parts in inventory, (and if not add the missing items to the "shopping list")
3.  update inventory (because some parts are already allocated, do they're not available for other processes anymore.

Messy, maybe, but are there other ways of doing it? I think that if I didn't use a cursor, I could end up allocating resources to something when the resources no longer were available.

Am I at least close to right on this?

Thanks!

Pieter

• drew.allen

SSC Guru

Points: 76709

I believe that this is a variation on the bin packing problem.  I think it's possible to solve without using a cursor, but I would need sample data and expected results.

Drew

J. Drew Allen