Which is the active price for each ingredient

Question

Which is the active price for each ingredient

pietlinden

SSC Guru

Points: 63503
More actions
October 3, 2024 at 6:42 pm

#4465041
Sorry for the lame title...
I'm working on the same bakery problem as before.
Manufacture--(M,1)--Product--(1,M)--ProductIngredient--(M,1)--Ingredient--(1,M)--Purchase
Manufacture (ProductID, Date, Quantity)
Purchase(IngredientID, Date, UnitWeight, UnitPrice)
So basically I have ingredients entering inventory from Purchases, and being used up in Manufacture. Is there an easy(ish) way of keeping track of which "bin" (well, an ingredient at a given price) is being used? I know I could do something kinda insane and track Usage and Waste and do a running sum and subtract and all that to get the "current bin", but wow that's a lot. is that the only way to do it?
Since I know someone's going to say, "but show us your code!", here's a really simple bit. Sure there are more ingredients but ... (Yes, I know this code isn't great... for illustrative uses only!)
```
use tempdb;
go


CREATE TABLE Purchases(
IngredientID INT, 
UnitWeight INT, 
UnitPrice MONEY, 
UnitsPurchased INT, 
PurchaseDate DATE);
GO

CREATE TABLE Usage (
IngredientID INT, 
UsageDate DATE, 
WeightUsed INT);
GO

INSERT INTO Purchases VALUES
(1,50,24.00,3,'1/1/2020'),
(1,50,25.00,2,'1/15/2020');

INSERT INTO Usage 
VALUES (1,'1/2/2020',40),
(1,'1/3/2020',30);

SELECT x.IngredientID
, x.TheDate
, x.IngredientWeight
,rtInOut = SUM(x.IngredientWeight) 
                  OVER (PARTITION BY x.IngredientID 
  ORDER BY x.TheDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
(SELECT p.IngredientID
, TheDate = p.PurchaseDate
, IngredientWeight = p.UnitWeight * p.UnitsPurchased
FROM Purchases p
UNION ALL
SELECT u.IngredientID, u.UsageDate, -1 * u.WeightUsed 
FROM Usage u) x
```
I suppose one way is to treat Ingredient like a slowly changing dimension where there are two keys in the Ingredient table (one for "this ingredient, regardless of price", and the other "this ingredient, at this price, over this time frame"). Then I'd just have a running sum of "usage" and from that I could figure out where I am in the "bins", and so find the unit cost of the different "segments" of ingredient (some from bin 1, some from bin 2 if bin 1 doesn't have enough) to determine cost?
(Glad data warehousing and database design is "easy"!)
- This topic was modified 1 month, 2 weeks ago by pietlinden.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Here is the beginning of a solution. It uses James F. Allen's (no relation) Interval Algebra: https://en.wikipedia.org/wiki/Allen%27s_interval_algebra.

The amount used from each bin depends on the relation between the purchases and the usage. In general it's the difference between the lower of current purchases and current usage and the higher of the previous purchases and previous usage.

I assume that you'll want to aggregate over the usage.

WITH Purchases AS
(
    SELECT p.IngredientID
         , p.UnitWeight
         , p.UnitPrice
         , p.UnitsPurchased
         , p.PurchaseDate
         , u.UnitWeightTotal
         , UnitWeightPrevTotal = COALESCE(SUM(u.UnitWeightTotal) OVER(PARTITION BY p.IngredientID ORDER BY p.PurchaseDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
         , UnitWeightCurTotal = SUM(u.UnitWeightTotal) OVER(PARTITION BY p.IngredientID ORDER BY p.PurchaseDate ROWS UNBOUNDED PRECEDING)
    FROM #Purchases AS p
    CROSS APPLY (VALUES(p.UnitWeight * p.UnitsPurchased)) u(UnitWeightTotal)
)
, Usage AS
(
    SELECT u.IngredientID
         , u.UsageDate
         , u.WeightUsed
         , WeightUsedPrev = COALESCE(SUM(u.WeightUsed) OVER(PARTITION BY u.IngredientID ORDER BY u.UsageDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
         , WeightUsedCur = SUM(u.WeightUsed) OVER(PARTITION BY u.IngredientID ORDER BY u.UsageDate ROWS UNBOUNDED PRECEDING)
    FROM #Usage AS u
)
SELECT *
     , IntervalRelation = CASE WHEN p.UnitWeightPrevTotal = u.WeightUsedPrev AND p.UnitWeightCurTotal > u.WeightUsedCur THEN 'Purchases is started by Usage'
                               WHEN p.UnitWeightPrevTotal < u.WeightUsedPrev AND p.UnitWeightCurTotal > u.WeightUsedCur THEN 'Purchases contains Usage'
                               ELSE 'Refer to https://en.wikipedia.org/wiki/Allen%27s_interval_algebra for other relationships.'
                           END
FROM Purchases AS p
INNER JOIN Usage AS u
    ON p.UnitWeightPrevTotal < u.WeightUsedCur
    AND u.WeightUsedPrev < p.UnitWeightCurTotal

Drew

Edited to use the correct relationship. Originally used the inverse relationship.

This reply was modified 1 month, 2 weeks ago by drew.allen.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Oh, I think I get it. You're doing a running total of Purchased and subtracting a running total of Used. Easy enough. Where does the UnitPrice come into this? that's the part that's really hard. (Or is this where I should be saying "That's why people use software like MAS90 for this kind of thing!") If I have a running total of Purchases and another of Usage, I can (in theory) divide WeightUsed by packageWeight and get the "current" package number (so I know where I am in the "usage queue"... well, if I include waste), Otherwise, how do I determine how much the "currently used unit" costs? I could explode the number of units purchased with a theta join, but do I need to do that?

Thanks!

Pieter

drew.allen SSC Guru Points: 76985 More actions · Answer 3

This is where it would help to have sample data. There are 9 different overlapping relationships, and each usage can have multiple pieces with various instances of those 9 relationships. You would have to take the UnitPrice for each piece and the number of units used by each piece to calculate the weighted UnitPrice for the usage.

Drew

PS: Sorry for the delay in posting, I was celebrating Indigenous Peoples Day.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Drew,

Okay, sorry, I'll bash that bit out in a little. I hope.. Just a lot going on right now.

pietlinden SSC Guru Points: 63503 More actions · Answer 5

I think I understand what I did wrong.

Between Purchases and Usage is maybe an "allocation" table, where I allocate one or more "blocks" of ingredient to each "BakeEvent" (just another name for a manufacturing event)

Allocation(AllocationID (PK), IngredientID (FK), LoafBatchKey, Weight)

BakeEvent(LoafBatchKey INT IDENTITY, LoafKey INT, BatchCount INT)

Then I can use Allocation to fulfill a request for a weight of ingredient and break it into one or more requests for ingredients at different prices. (Allocation basically breaks the many-t0-many relationship between Purchase and BakeEvent.) So I think that works.