carrying metric through a table

Hi. this is a tricky one to explain but I'm at a bit of a loss

Consider a product demand table such as
Product Demand Qty
Prod 1 500
Prod 2 300
Prod 3 0

Now consider a Inventory table such as
Product Batch Total qty Free qty
Prod 1 Batch 1 300 0
Prod 1 Batch 2 300 100
Prod 1 Batch 3 200 200
Prod 2 Batch 22 600 300

Wha I am trying to achieve is the "Free Qty" column such that, within each product, the demand is "comsumed" by each batch in turn until completly comsumed (or no batches are left). Hence the demand of 500 would result in 300 going to zero for batch 1, carry forward 200 demand which would be completly comsuemd by batch 2 leaving 100 "free". Subsequnet batches would remain all free for that first product. Then the operation continues for the next product.

How on earth do I do it ???

Thanks for you thoughts

GerryJust guessing, but I can't get mental "traction" on this problem. I passed on trying to answer, to let somebody else get a clear shot at it. Since nobody else has posted, I'm betting that they can't get a handle on it either. Maybe it would help if you explain just a wee bit more ?!?

-PatP|||Thanks Pat. I'll try

Perhaps I shoudl explain the physical process.
you have a food store and you have all the "sell buy" dates for each incoming shipment. You have some orders for a given quantity and you want to simulate a FIFO (First in First out) process of use. So the "free to sell" of the oldest stock is reduced by the open orders leaving the newest stock to last. In fact I'm talking about electronic devices and the physical date of manufacture but the analogy is good. Does that explain ?

Gerry|||[QUOTE][SIZE=1]Originally posted by GerryP
Thanks Pat. I'll try

Perhaps I shoudl explain the physical process.
you have a food store and you have all the "sell buy" dates for each incoming shipment. You have some orders for a given quantity and you want to simulate a FIFO (First in First out) process of use. So the "free to sell" of the oldest stock is reduced by the open orders leaving the newest stock to last. In fact I'm talking about electronic devices and the physical date of manufacture but the analogy is good. Does that explain ?

TO put it another way. here is a slice of the documentation I ma doing (ahead of the code.. Weird!)

Take a demand of 1000 and a Batch content as here
Batch Qty Free
Batch 1 500 0 (1000-500 carried forward demand= 500)
Batch 2 300 0 (500 300 Carried forward demand= 200
Batch 3 450 250 All consumed: nothing carried forward
Batch 4 400 400 All consumed: nothing carried forward|||CREATE TABLE Demand(
Product smallint PRIMARY KEY,
Qty smallint)

INSERT Demand
SELECT 1, 500
UNION ALL SELECT 2, 300
UNION ALL SELECT 3, 0

CREATE TABLE Inventory(
Product smallint,
Batch smallint,
Totalqty smallint,
PRIMARY KEY(Product,Batch))

INSERT Inventory
SELECT 1, 1, 300, 0
UNION ALL SELECT 1, 2, 300, 100
UNION ALL SELECT 1, 3, 200, 200
UNION ALL SELECT 2, 22, 600, 300

SELECT i.Product, i.Batch, i.TotalQty,
CASE
WHEN
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) <= d.Qty
THEN 0
WHEN
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty < TotalQty
THEN
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty
ELSE TotalQty
END FreeQty
FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

If there will always be row in Demand for each product, then just use inner join instead of left join.|||It worked a DREAM.
I think you missed out defining a fourth metric in Inventory but thats fine. It's good for me to add just a little value.

My question now is.. How does it work ?
reformating the core somewhat we see
SELECT i.Product, i.Batch, i.TotalQty,
CASE
WHEN
(SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) <= d.Qty THEN 0
WHEN
(SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty < TotalQty
THEN
(SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty
ELSE TotalQty
END FreeQty
FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

what is not clear is how it carries the 'remainder' down from one batch to the next ? There is no question it works... but how ?

Thanks again

Gerry|||Do not use code unless you understand it.

Try this query to understand value of subquery:

SELECT i.Product, i.Batch, i.TotalQty, d.Qty,
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) CumulativeTotal
FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

Unless this is static OLAP database, FreeQty column should not be denormalized into Inventory table because that will add complexity to OLTP code and hinder performance.|||Ah. The proverbial penny has dropped and I understand !

I've applied it to my real world example with excellent results

Thanks for your help and have a good weekend

Gerry