September 18, 2019 at 4:50 am
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
September 18, 2019 at 11:46 am
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
September 18, 2019 at 8:11 pm
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.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 19, 2019 at 3:00 am
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
September 19, 2019 at 3:21 pm
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.
September 19, 2019 at 9:10 pm
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
Please post DDL and follow ANSI/ISO standards when asking for help.
September 20, 2019 at 3:44 am
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!)
September 23, 2019 at 3:05 pm
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.
September 23, 2019 at 5:47 pm
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;
go
CREATE 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 TotalNeeded
FROM
(SELECT dtl.SalesOrderID,
dtl.ProductID,
BOM.ComponentID,
QuantityNeeded = dtl.OrderQty * BOM.PerAssemblyQty
FROM
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 completed
INNER JOIN #InvoiceList il ON dtl.SalesOrderID = il.InvoiceID
) inv
GROUP BY inv.ComponentID
ORDER 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
September 24, 2019 at 6:11 am
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 procedure
CREATE TABLE #ProcessOrders (OrderToProcess INT PRIMARY KEY);
GO
INSERT INTO #ProcessOrders(orderToProcess) VALUES (43659),(43660);
--- now I can actually answer the question
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 cte_BOM.ProductID,
[Name],
Color,
Quantity * sod.OrderQty AS Total_Qty,
ProductLevel,
ProductAssemblyID,
Sort
FROM cte_BOM
-- inner join to a list of products & quantities
INNER JOIN Sales.SalesOrderDetail sod ON sod.ProductID = cte_BOM.ProductAssemblyID
WHERE 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.
September 25, 2019 at 10:23 pm
If I sequence the Work Orders somehow, I could use one of those dreaded cursor things... then I could do something like this:
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
September 26, 2019 at 4:20 pm
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
Business Intelligence Analyst
Philadelphia, PA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply