October 10, 2024 at 4:03 am
Sorry, more bakery stuff. Say I buy ingredients over time and I want to know how the total ingredient cost for each batch. The tricky part is that some ingredient prices are crazy volatile (flour increases in price by half over about 18 months). So I wanted to use up the oldest ingredients first (at the corresponding purchase price).
Is this simple and I just don't get it? (Well, I did see Dave Ballantyne's article about it, and it hurt my brain, so maybe not?)
The only way I could think of solving it was by basically "binning" the purchases of ingredient by price and allocating it in purchase order (FIFO). Can I do that with a CTE? Or would I be better off with a cursor? (I saw Luis C's article on that, too). Getting the most recent purchase price is super easy, but that's not accurate, because I can buy (say)5 fifty pound bags of flour one week, and then buy another 10 of them the next week at a different unit price, and of course that screws things up.
Is there a good article on this somewhere? (I thought it would be in Kathi Kellenberger's book, but I don't think so...) I have Itzik's book too (T-SQL Querying), but I didn't see it in there either.
I'd post some code, but I kinda wanted to do my homework first. I didn't find anything and was just wondering if I was missing something.
October 10, 2024 at 6:42 am
fwiw - post this question as input for copilot . microsoft . com and add "Write the tsql stored procedure to process my expenses report per batch"
See what it comes up with and study that code.
It may not be correct at all, it may not be performing well, but it may get you on the right track ( even if knowing "how not to" can be your lead )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 10, 2024 at 7:30 am
I requested it to write the TSQL DDL to created the needed tables based on your input.
It came up with this. ( not perfect, but it gets you started )
-- Assuming you have three main entities: Ingredients, Purchases, and Batches
-- Table to store Ingredient details
CREATE TABLE Ingredients (
IngredientID INT IDENTITY(1,1) PRIMARY KEY,
IngredientName varchar(100) NOT NULL,
Unit varchar(50) NOT NULL -- Example: kg, lbs, etc.
);
-- Table to store Purchases details
CREATE TABLE Purchases (
PurchaseID INT IDENTITY(1,1) PRIMARY KEY,
IngredientID INT NOT NULL,
PurchaseDate DATE NOT NULL,
Quantity DECIMAL(10, 2) NOT NULL,
UnitPrice DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (IngredientID) REFERENCES Ingredients(IngredientID)
);
-- Table to store Batch details
CREATE TABLE Batches (
BatchID INT IDENTITY(1,1) PRIMARY KEY,
PurchaseID INT NOT NULL,
BatchDate DATE NOT NULL,
UsedQuantity DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (PurchaseID) REFERENCES Purchases(PurchaseID)
);
Then I asked it to write a tsql stored procedure to produce the requested report.
It came up with this:
CREATE PROCEDURE ProcessExpenseReport
AS
BEGIN
DECLARE @IngredientID INT;
DECLARE @BatchID INT;
DECLARE @QuantityNeeded DECIMAL(10, 2);
DECLARE @RemainingQuantity DECIMAL(10, 2);
-- Cursor to iterate through each batch
DECLARE BatchCursor CURSOR FOR
SELECT
BatchID,
IngredientID,
UsedQuantity
FROM Batches;
OPEN BatchCursor;
FETCH NEXT FROM BatchCursor INTO @BatchID, @IngredientID, @QuantityNeeded;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RemainingQuantity = @QuantityNeeded;
-- Subquery to get purchases in FIFO order
;WITH CTE_Purchases AS (
SELECT
PurchaseID,
Quantity,
UnitPrice,
ROW_NUMBER() OVER (PARTITION BY IngredientID ORDER BY PurchaseDate) AS RowNum
FROM Purchases
WHERE IngredientID = @IngredientID
)
-- Cursor to iterate through each purchase
DECLARE PurchaseCursor CURSOR FOR
SELECT
PurchaseID,
Quantity,
UnitPrice
FROM CTE_Purchases
WHERE RowNum > 0;
OPEN PurchaseCursor;
FETCH NEXT FROM PurchaseCursor INTO @PurchaseID, @Quantity, @UnitPrice;
WHILE @@FETCH_STATUS = 0 AND @RemainingQuantity > 0
BEGIN
DECLARE @UsedFromThisPurchase DECIMAL(10, 2);
IF @RemainingQuantity <= @Quantity
BEGIN
SET @UsedFromThisPurchase = @RemainingQuantity;
END
ELSE
BEGIN
SET @UsedFromThisPurchase = @Quantity;
END
-- Update remaining quantity
SET @RemainingQuantity = @RemainingQuantity - @UsedFromThisPurchase;
-- Log the usage (this can be tailored to your logging table/needs)
INSERT INTO BatchExpenseLogs (BatchID, PurchaseID, QuantityUsed, Cost)
VALUES (@BatchID, @PurchaseID, @UsedFromThisPurchase, @UsedFromThisPurchase * @UnitPrice);
FETCH NEXT FROM PurchaseCursor INTO @PurchaseID, @Quantity, @UnitPrice;
END
CLOSE PurchaseCursor;
DEALLOCATE PurchaseCursor;
FETCH NEXT FROM BatchCursor INTO @BatchID, @IngredientID, @QuantityNeeded;
END
CLOSE BatchCursor;
DEALLOCATE BatchCursor;
END;
Also not perfect, but it gets you started.
Then I asked it to write tsql insert statements for test data foor all above objects:
-- Insert test data into Ingredients table
INSERT INTO Ingredients (IngredientName, Unit)
VALUES
('Flour', 'kg'),
('Sugar', 'kg'),
('Butter', 'kg');
-- Insert test data into Purchases table
INSERT INTO Purchases (IngredientID, PurchaseDate, Quantity, UnitPrice)
VALUES
(1, '2023-01-10', 50, 0.75), -- Flour
(1, '2023-02-15', 30, 0.80), -- Flour
(2, '2023-01-12', 20, 1.00), -- Sugar
(2, '2023-03-05', 40, 1.10), -- Sugar
(3, '2023-01-20', 10, 1.50), -- Butter
(3, '2023-04-01', 15, 1.60); -- Butter
-- Insert test data into Batches table
INSERT INTO Batches (PurchaseID, BatchDate, UsedQuantity)
VALUES
(1, '2023-05-01', 20), -- Batch using Flour from first purchase
(2, '2023-06-10', 10), -- Batch using Flour from second purchase
(3, '2023-05-15', 10), -- Batch using Sugar from first purchase
(4, '2023-07-20', 20), -- Batch using Sugar from second purchase
(5, '2023-06-05', 5), -- Batch using Butter from first purchase
(6, '2023-08-15', 8); -- Batch using Butter from second purchase
As you will see, it will guide you to a valid solution.
You will need to fiddle around and set it to produce the data and meta data you want.
Try to write a set based sql solution to compare its performance to the suggested solution
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 10, 2024 at 1:59 pm
Did you read the response I made to your other thread? https://www.sqlservercentral.com/forums/topic/which-is-the-active-price-for-each-ingredient#post-4465061
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2024 at 11:00 pm
Drew,
yes, I did. Sorry, just been doing other stuff. I'll go look at it and add questions to that one. Because I know I'm going to be confused.
Sorry about that!
Thanks,
Pieter
October 12, 2024 at 6:36 am
This was removed by the editor as SPAM
October 22, 2024 at 9:02 pm
Johan,
Sorry for taking forever to get back to you, but I get it. It was pretty much what I expected. I need to use a cursor to loop over the purchases and subtract... I was thinking it would make more sense to lump the purchase quantities (well, weights) into lots (so if I buy 300# of flour, I take from that as a single unit, because it'll all have the same unit price). So I loop over it using a cursor so that I can split an "allocation" into two parts - one from the last "bin" or whatever and one allocation from the next one (that completes the order).
I'll play around with it. (From there just "log" the usage so that I have a batchID, an ingredientID and one or more bins and weights (so batchID, ingredientID, "binID", UnitPrice, Weight). And then I just sum that to get the total cost for a batch kind of thing.
Thanks!
Pieter
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply