June 18, 2025 at 3:13 am
I'm trying to figure out a how to do average costing over time in T-SQL... I'll use up ingredient inventory over time, and every six weeks buy more. So the "leftover" ingredients would be valued at [Weight Remaining] * [Per Kg Price]. Then when I buy new inventory, the new total value would be
[Weight Remaining] * [Per Kg Price] + [Weight Purchased] * [New Per Kg Price] / ([Weight Remaining] + [Weight Purchased])
that part is fine. But do I just store the averaged unit price and weight on hand back into the Ingredient table (Just feels soooo wrong!) How do I track the quantity on hand over time? I can do a running total using a windowing function. Is that the trick?
Any pointers on how to handle that so that I get a reasonable number for costing? (links to articles or whatever is fine... I don't really have a clue what I'm doing here.)
thanks,
Pieter
June 18, 2025 at 3:51 pm
Are you able to provide some sample data, and DDL and DML, as what results you might be expecting for those values? I'm having some difficulty visualising what your data looks like, which makes giving a solution even harder.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 19, 2025 at 6:51 pm
Without seeing your data (or sample or DDL or DML), I'm just guessing here but I see this as a multiple problem thing.
First you want to know if you need to store the current "total value" to the inventory table. My opinion - no. I would either do a calculated column for it or toss it in a view. Data that changes over time like that if persisted to the table will require an update to the value any time you change weight remaining.
I also see some problems with your design for that total value. The problem I see is the long term values. Lets say you have an item called X that initially you bought 10 for 10. You see demand for it coming up, so you buy 5 more for 5. All is good now, but before you sell any, you buy 2 more for 20. You now have 3 different prices and 3 different quantities. Your formula breaks because when you calculate it for the new 2 for 20, which previous weight and price do you use? Do you average them?
But ignoring that, to report on change over time requires you to capture data changes. So you'd likely want to set up an audit table and a trigger on the table. So each insert, update, and delete would get written to an audit table as well as the current inventory table and you'd build your change over time reports off of the audit data. I mean, you COULD capture the data updates as negative quantities in the table (ie row 1 is quantity 10, row 2 is quantity -2 and you'd just SUM it up with a windowing function and get 8 (10-2), but that'll get slower the larger the table gets unless you purge data every now and then such as when the quantities hit 0, all rows related to that item get deleted, but then I'd want an item table and a quantity table with a FK constraint to map the item to the quantities so my item table doesn't get HUGE over time).
A lot of it really depends on how you designed your tables and your SP's and such...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 19, 2025 at 7:33 pm
Right now the tables are kind of wrong, but this is a rough outline:
CREATE TABLE Ingredient (
IngredientID INT IDENTITY(1,1) NOT NULL,
IngredientName VARCHAR(25) NOT NULL,
IngredientWeightKg DECIMAL(5,2),
VolumeL DECIMAL(5,2)
CONSTRAINT pkIngredient PRIMARY KEY (IngredientID));
CREATE TABLE Purchase (
PurchaseDate DATE NOT NULL,
InvoiceNo CHAR(20) NOT NULL,
IngredientID INT NOT NULL,
UnitPrice DECIMAL(5,2) NOT NULL,
Quantity INT NOT NULL
CONSTRAINT pkPurchase PRIMARY KEY (PurchaseDate, InvoiceNo, IngredientID)
fkIngredient FOREIGN KEY IngredientID REFERENCES Ingredient(IngredientID)
);
CREATE TABLE Product (
ProductID INT IDENTITY(1,1) NOT NULL,
ProductName VARCHAR(50) UNIQUE NOT NULL
CONSTRAINT pkProduct(ProductID));
CREATE TABLE Recipe (
ProductID INT,
IngredientID INT,
IngredientWeightGrams INT
CONSTRAINT pkRecipe PRIMARY KEY(ProductID,IngredientID) );
Generally speaking, since there just isn't enough space to store tons of inventory, he buys what he needs and uses it up. There are bulk ingredients that he buys every 6 weeks, but since he doesn't hoard ingredients, there's never really a case when he buys like 24 50 pound bags of flour, uses a few then buys 24 more, and then does it again. It just doesn't happen. He'll have some on hand (maybe 100 or 150 pounds) and buy more.
I can track what he makes really easily...
CREATE TABLE BakeSheet(
BakeDueDate DATE NOT NULL,
ProductID INT NOT NULL,
BatchCount INT NOT NULL
CONSTRAINT pkBakeSheet PRIMARY KEY (BakeDueDate, ProductID)
);
I have a cheat view that deals with the whole starter mess that returns just the raw ingredients (and no intermediate ones, like starter), and that works fine. (vwRecipeBaseIngredients)
I can figure out how much of each ingredient is used (theoretically) by using the BakeSheet.BatchCount and the view vwRecipeBaseIngredients and multiplying the BatchCount * RecipeIngredientWeight... that's fine.
So I was thinking I could determine what's left in inventory before a purchase happens (Just weigh the open stuff and the rest is easy... Qty * Unit Weight...) So then I get the most recent price for the stuff on hand, multiply and I get a dollar value. Then I do same with the just purchased items. So I have WeightPurchased, PurchaseUnitPrice, WeightOnHand, LastPurchaseUnitPrice / UnitWeight ... then I just do a sort of weighted average (OnHand P*Q + Purchased P*Q)/(OnHand Q + Purchased Q) and I end up with a new unit cost. (Yes, I realize it's not perfect, but he's not making thousands of loaves every week.)
Do I just have to basically track the "lots" (the entire weight that's purchased at the same price per unit)? I don't think it has to be 100% perfect... it's to figure out what a loaf of bread costs to make. (And then on top of that are the rest of the costs... but one problem at a time!) The "lot" would be pretty much the (IngredientID, PurchaseDate, TotalWeightPurchased) So any ingredient would come from at most two different purchase "batches" (Would you like some nice moldy year old flour? Umm... no thanks!) I guess I could do something like a cursor (yeah, dirty word) and get the "rest" of one batch and then the remaining weight from the next batch and just add two separate line items into my Costing bit. (So I could have one part at say $2.00/pound and another at $2.15/pound.... but either way, it's Weight1 * $2.00/lb + Weight2 * $2.15/lb... ) Then I just have the ability to have more than one "instance" of an "issued" ingredient for a given bake "event".
Is that a better way of doing it? My ultimate goal is to determine with reasonable accuracy what a loaf of bread costs to make.
June 19, 2025 at 9:42 pm
You say that he doesn't have cases where he would have 3 or more values needed for calculating the average, but that is current state. What happens in 5 years? What if he decides to open a second branch? My advice is to plan for the future wherever possible. I would try to design your system so you can handle a future state where he is super successful. Might be more work up front, but I have found it is often better to design the system with the future in mind so you don't get bit trying to make it work later.
BUT it is your call how you do that. Nothing wrong with building exactly to the requirements, but it may mean a complete overhaul in the future.
As for getting the data for 2 batches, that doesn't sound too hard to do if you know it is ONLY 2 batches. It means you will have 2 rows per ingredient ID containing purchase date, total weight purchased, and presumably the purchase cost. I personally would avoid the cursor and instead do a self join on the table by the ID and date being greater than current date. Then if the joined data is NULL, that's the most recent order. If the joined date is equal to the max data date, that's the second newest order. The rest is garbage data IF you are comfortable assuming you don't care about orders older than that.
Alternately, you could have a trigger on the table that gets triggered before an INSERT that would set a flag "stale" or "unused" or whatever you want to call it that would be used to indicate the row is not to be included in the calculations. Then you should be able to do the math on the rows where the flag = 0 and group by the ID or use a windowing function over the ID depending on your need.
I would advise you to plan for the future UNLESS you are on a really tight time crunch for this. Making your system handle multiple sites or adding in additional quantity/price values for your calculation can be tricky if you design it and lock yourself into 2 and 1 site.
Also, just to confirm with you, will there ever be a case where 1 invoice will have the same item listed multiple times (not multiple quantities, but the item more than once)? I can see someone entering the data for a 100+ invoice and putting the same item on there twice because there were 2 different quantities needed. This will fail to insert into your Purchase table. Similar with the recipe table - will a recipe ONLY list an ingredient once? I've had some recipes that call for one ingredient more than once as it is used in multiple places with different quantities. Cheese bread would be a good example as you have SOME mixed into the bread and SOME gets put on top of the bread. 1 recipe and the ingredient "Cheese" is listed twice.
EDIT - I also would recommend discussing with the client how accurate they need the numbers to be and how "real-time" they need to be. I wouldn't just say things are good enough - make sure the end user understands the math behind it and the limitations/flaws with the implementation. Get their sign off on that so you don't end up being blamed when the system tells him to sell for more or less than he really should be. Do what you can to make yourself blameless in the event your system messes up his finances.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 9, 2025 at 11:39 pm
Okay, I'm an idiot. It's super simple.
Take the total value of the new inventory
((Unit Price / Unit Weight * Qty Purchased) + (Per Kg Price * Weight On Hand) ) / (New Weight + Weight On Hand)
whoa. super difficult. LOL. And since he only gets bulk stuff every six weeks, not super hard.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply