[Joe Celko’s Quantity on Hand Puzzle]
You are now the guy in charge of the company inventory. You get requisitions that tell how many widgets people are putting into a bin or taking out of it on a given date. Sometimes the quantity is positive (returns), sometimes the quantity is negative (withdrawals).
CREATE TABLE Requistions (reqdate DATE NOT NULL, qty INTEGER NOT NULL CHECK (qty <> 0) );Your job is to provide a running balance on the quantity on hand as an SQL column. We want something like:
Inventory reqdate qty qty_on_hand ======================================== 1993-11-01 100 100 1993-11-02 120 220 1993-11-03 -150 70 1993-11-04 50 120 1993-11-05 -35 85
This is really a quickie with the SQL-92 feature of using a scalar query in the SELECT list.
SELECT reqdate, qty, (SELECT SUM(qty) FROM Inventory AS S2 WHERE S2.reqdate <= S1.reqdate) AS qty_on_hand FROM Inventory AS S1 ORDER BY reqdate;If the innermost query comes back empty, it is a NULL. This interesting feature of the SQL-92 standard sometimes — not always! — lets you write an OUTER JOIN as a query within the SELECT clause. For example, this query will work only if each customer has one or zero orders:
SELECT custno, custname, (SELECT orderamt FROM Orders WHERE Customers.custno = Orders.custno) FROM Customers;and give the same result as
SELECT custno, custname, orderamt FROM Customers LEFT OUTER JOIN Orders ON Customers.custno = Orders.custno;
Puzzle provided courtesy of: