I am looking for a solution to this problem using the SQL-99 OLAP
operators. You start with these two tables:

CREATE TABLE Inventory
(store_id INTEGER NOT NULL PRIMARY KEY,
item_qty INTEGER NOT NULL CHECK (item_qty >= 0));

INSERT INTO Inventory (store_id, item_qty)
VALUES (10, 2),(20, 3), (30, 2);

CREATE TABLE Orders
(customer_id CHAR(5) NOT NULL PRIMARY KEY,
item_qty INTEGER NOT NULL CHECK (item_qty > 0));

INSERT INTO Orders (customer_id, item_qty)
VALUES ('Bill', 4), ('Fred', 2);

What we want to do is fill Bill's order for 4 units by taking 2 units
from store #1, and 2 units from store #2. Next we process Fred's order
with the 1 unit left in store #1 and 1 unit from store #3. In SQL-92,
one answer is:

SELECT I.store_id, O.customer_id,
(CASE WHEN O.end_running_qty <= I.end_running_qty
THEN O.end_running_qty
ELSE I.end_running_qty END
- CASE WHEN O.start_running_qty >= I.start_running_qty
THEN O.start_running_qty
ELSE I.start_running_qty END)
AS items_consumed_tally
FROM (SELECT I1.store_id,
SUM(I2.item_qty) - I1.item_qty,
SUM(I2.item_qty)
FROM Inventory AS I1, Inventory AS I2
WHERE I2.store_id <= I1.store_id
GROUP BY I1.store_id, I1.item_qty)
AS I (store_id, start_running_qty, end_running_qty)
INNER JOIN
(SELECT O1.customer_id,
SUM(O2.item_qty) - O1.item_qty,
SUM(O2.item_qty) AS end_running_qty
FROM Orders AS O1, Orders AS O2
WHERE O2.customer_id <= O1.customer_id
GROUP BY O1.customer_id, O1.item_qty)
AS O (store_id, start_running_qty, end_running_qty)
ON O.start_running_qty < I.end_running_qty
AND O.end_running_qty > I.start_running_qty
-- ORDER BY store_id, customer_id;

I am pretty sure this can also be done with the new SQL-99 OLAP
operators. Anyone got an answer?