Component/Ingredient Allocation - do I need a cursor for this?

[madpiet]

<intro>
I'm modeling the use/consumption of ingredients in a bakery... Each recipe requires a set of (ingredient, Weight) to yield a number of loaves. I have a table of Purchases (PurchaseID, VendorID, PurchaseDate, IngredientID, UnitPrice, UnitsPurchased). When I "use/allocate" ingredients from inventory to a batch (BatchID, RecipeID, BatchCount, Date), I want to basically do a First in First Out (FIFO), so one "allocation" may span two (or more) purchases of ingredient at different prices.

There are purchases of Product at one or more price points. I can use up one "bin" (all the weight at one price point), and then move on to the next "bin". Can I do that without using a cursor? I know I can do FIFO kind of by using TOP(n), but it's hard because what happens when the "top" say 60# that I may need for a batch can come from two different "bins". Can I do that without a cursor? If I used a cursor, I'd get a "set" of ingredients where there are some in stock, maybe at two different price points. I could then take all of whats left in the first "bin" and then take the rest of what I need from the next newest "bin"


CREATE TABLE Purchases (
IngredientID INT NOT NULL
,PurchaseDate DATE NOT NULL
,UnitPrice SMALLMONEY
,UnitsPurchased INT
-- ,UnitWeight DECIMAL()
);


-- all of the unit prices are
INSERT INTO Purchases (IngredientID, PurchaseDate, UnitPrice, UnitsPurchased, UnitWeight)
VALUES (1, '01-Jan-2022', 48.00, 6, 50),
(1, '08-Jan-2022', 48.00, 12, 50),
(1, '15-Jan-2022', 49.00, 12, 50),
(1, '31-Jan-2022', 50.00, 6, 50);


LOTS: (1, 48.00, 18, 50), (1, 49.00, 12, 50), (1, 50.00, 6, 50)

so the "lots" (single ingredient at one unit price)


SELECT IngredientID
,UnitPrice
,TotalUnits = SUM(UnitsPurchased)
,TotalCost = SUM(UnitsPurchased * UnitPrice)
FROM Purchases
GROUP BY IngredientID, UnitPrice;


Then I'd sort the bins by PurchaseDate, and then take out of the "top"/first bin until the bin is empty and go to the next.


So if I needed 350# of ingredientID = 1, I'd need the full Jan 1 lot and then 50# from the second. Can I do that without a cursor?
I know I can create a running total using (say) a CTE, but can I then use that to determine what the total cost of a batch of bread? The part I don't understand how to do is to determine which lot (and at what unit price) I allot that to a batch.


Can I do this without a cursor? If so, how? I could use the cursor to allocate the different lots of ingredient to a batch and decrease the stock part too.


CREATE TABLE PurchaseFact (
PurchaseID INT IDENTITY(1,1)
,IngredientID INT NOT NULL
,UnitPrice SMALLMONEY NOT NULL
,UnitsPurchased INT
,PurchaseDate DATETIME NOT NULL
,VendorID INT NOT NULL
)



Here's the Recipe table


CREATE TABLE [dbo].[RecipeIngredient](
[RecipeID] [int] NOT NULL,
[IngredientID] [int] NOT NULL,
[RecipeIngredientWeight] [int] NOT NULL
) ON [PRIMARY];


One thought I had was to keep track of the weight allocated out of each "lot" (Purchase of an ingredient). I can calculate the total weight really easily - it's just Purchase.Units * Ingredient.UnitWeight)

So how would I do the allocations? Would I basically have to use a cursor to get from the first Purchase Order with any weight that's not been allocated and go that way? (Kind of like using a recordset in Access and looping over purchase orders, "allocating" whatever's left of the PO weight for that ingredient, and going to the next if there isn't enough weight left from that purchase order, and then go on to the next one? (and logging the (PONumber, IngredientID, Weight, BatchID) so I know what the weight was allocated to.)

Does it even make sense to do something "Get me all the "requests" for ingredients in this batch where PO.WeightRemaining is sufficient to serve the Allocation.WeightRequired request and do the whole thing as a cursor/recordset where I loop over the Allocations in the cursor/recordset and go to the next Purchase Order if there isn't enough in the first one to meet the required weight?

The goal is to get the actual cost of the Ingredients for a given recipe. (and some ingredient prices are volatile, so just taking the most recent price is going to cause problems). Can I do this without a cursor? A normal batch of product will never use an entire package of anything (It's bread, and flour comes in 50 pound bags, and since he only makes like 400 loaves a week total, no bake order (ProductID, Date, BatchCount) will use a huge amount of any single ingredient.

If It's not clear what I'm talking about, go ahead and ask questions.

Thanks!
Pieter

[Minty]

I have done something similar with components for electronic assemblies stored in different locations.

If you know you need 400 of something and you only have 325 in location(price band/stock date) A then you know you that your first "pass" through the data will require a loop to find the balance (75 units).
So you remove the 325 from location A , then repeat the operation with your remaining qty.

I can't easily translate that to a set based operation. So yes I would loop through the process.
In SQL server I would process this in a SP and store the results in a #temp table which I would then use to display a "pick list", and ultimately drive the creation of the stock transaction records.

[madpiet]

That's what I thought. I was just wondering if I wasn't missing something. My thought is to do something like storing the remaining weight in a column in my purchase table (PurchaseID, (LotID?), IngredientID, PurchaseDate, VendorID, UnitPrice, {PackageWeight}, WeightRemaining). Otherwise I do something like a total of "deductions" from the lot... the important parts are getting the PerKgPrice and the IngredientID so I can do the simple P*Q stuff to get value.
All this stuff is going to end up in a data warehouse, if I can figure out how to do it... LOL

you wouldn't happen to know a good thorough tutorial on cursors, would you? <g>

Thanks, Minty!

[NoellaG]

RBAR alert. Try to avoid the use of cursors, I only use them if no other option and if I have to loop max 20 times.
For a start, you need a Stock table that you update with each purchase and use of ingredients. This is done with a simple update or insert (for new ingredients) statement. The price of the current stock line can be recalculated each time as Quantity Existing Stock * Price Existing stock + Quantity added stock * Price added stock divided by Existing quantity + Added quantity.
But please don't go looping through the records with a cursor. SQL server is batch orientated and extremely slow when processing row by row. Don't kill the engine.

[madpiet]

RBAR alert. Try to avoid the use of cursors, I only use them if no other option and if I have to loop max 20 times.
For a start, you need a Stock table that you update with each purchase and use of ingredients. This is done with a simple update or insert (for new ingredients) statement. The price of the current stock line can be recalculated each time as Quantity Existing Stock * Price Existing stock + Quantity added stock * Price added stock divided by Existing quantity + Added quantity.
But please don't go looping through the records with a cursor. SQL server is batch orientated and extremely slow when processing row by row. Don't kill the engine.

Hence the question. I was wondering if RBAR was my only option. I suppose I could recalculate the value of what each ingredient weight in stock is worth. I suppose I could bastardize it and calculate an average price per kg or whatever, and just use that. Otherwise, tracking weight on hand is a bit of a joke (well, exactly how much flour goes into a loaf of bread? If you've ever made any, you know the problem) Oh, and the loop would run at most twice. Once for the "current" lot of ingredients and then maybe one for the next lot if the request cannot be satisfied from the first lot.

Yeah, Mr. RBAR (Jeff) is a friend of mine. Maybe I should ask him.

[madpiet]

RBAR alert. Try to avoid the use of cursors, I only use them if no other option and if I have to loop max 20 times.
For a start, you need a Stock table that you update with each purchase and use of ingredients. This is done with a simple update or insert (for new ingredients) statement. The price of the current stock line can be recalculated each time as Quantity Existing Stock * Price Existing stock + Quantity added stock * Price added stock divided by Existing quantity + Added quantity.
But please don't go looping through the records with a cursor. SQL server is batch orientated and extremely slow when processing row by row. Don't kill the engine.

As I said, I know that cursors are a weapon of last resort when you need to guarantee serialization (which flies in the face of good scalable programming). That's why I asked the question. I could do something like use

SELECT *
FROM RecipeIngredient ri
CROSS APPLY (SELECT TOP 2 *
FROM Purchases p
WHERE p.IngredientID = ri.IngredientID
ORDER BY PurchaseDate DESC)

so I only get two related records. The part I was wondering about was do I "expire" a lot (the entire purchase weight of an ingredient purchased on a single date, so at the same price) as I use them up. I suppose I could do a weighted average to get an approximation of the lot's value. I'll give it a try.

Oh, and I was just going to loop twice. Once for the "current" lot, and if there was not enough weight in the current lot to meet the request, I'd loop once for those.

Oh, I know! I'll speed everything up!!! I'll use a recordset in Access! (Yeah, that was a joke)

Thanks!

[Minty]

You only need the loop if there aren't enough units in the first batch you look at.
So you can easily batch process the entire dataset where that criteria applies.

If you keep track of those records and then remove them from your "fulfilment" list you can then process them in one hit as well as you know you will be using all the ingredients from that batch.

Only at that point do you need to repeat the exercise.
This would mean, from your description, worst case two passes?

[madpiet]

Yes, maximum two passes. I could do something like this in TSQL:

SELECT ri.IngredientID, ri.RecipeID, ri.Weight AS RequiredWeight, ca.PurchaseDate, ca.PricePerKg, ca.PricePerKg * ri.Weight AS IngredientCost
FROM RecipeIngredient ri
WHERE...
CROSS APPLY (SELECT TOP 2 *
FROM Purchases p
WHERE p.IngredientID = ri.IngredientID
ORDER BY p.PurchaseDate) ca

but a Lot will either have enough left (at a given price) to fulfill a request or it won't. So if it doesn't it'll require a loop to get weight from the next lot, and each will be recorded (batchID, DateTaken, Weight, (IngredientID) )... (Strictly speaking, IngredientID is a property of the Lot... just there to explain better. Oh, I'm a chucklehead. In the Data Warehouse version, the Ingredient is merged with Purchase (where the UnitCost comes from). So the Primary Key of each Ingredient changes when the (Lot) Unit Price changes. Maybe I need to go introduce myself to the Slowly Changing Dimension Wizard in SSIS?

Okay, so the Ingredient SCD becomes something like (IngredientLotID INT PK, IngredientID INT, LotPurchaseDate, LotPrice, LotUnits, LotWeight) Where "IngredientID" key points back to the original Ingredient table (well, kind of?), but since this is a DW, the columns just repeat. Then the whole Product--(1,M)--ProductIngredient (recipe)--(M,1)--Ingredient relationship... When an Ingredient price changes, it's treated as a new record (because a significant column (UnitPrice) value changes. So the whole "chain" gets updated. (Sorry, I might be thinking out loud)

Yeah, I was thinking I'd do something like log the "allocations" to a specific batch of bread (RecipeID, Quantity, Date)==> BatchID, and then I could have one or maybe two allocations of a single ingredient, where if there are two allocations, they're at different prices. I was thinking I could either (1) update the "Lot" by using a calculated column type thing "UnallocatedWeight" = [PurchaseWeight] - SUM(Allocations[AllocatedWeight])

Maybe I just gotta try it? LOL

[NoellaG]

Still think using a stock table would simplify a lot. After all it is the standard solution for a reason. I think it is rather strange you use a datawarehouse as a base for your transactions. Normally they are only used for reporting and analysis purposes. In each case, be careful with the isolation levels you work with. Using Serializable causes a serious amount of locking, and in combination with looping (even a limited amount) can cause deadlocks. More that I have the feeling that using the select results you are going to do some updates. Don't forget that locks are kept till the end of the transaction, and even when using query hints, if somewhere in the transaction you acquire an update lock, all following operations are with updlock.
Maybe lookinto row versioning?