Question:
[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

Answer:
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:
Joe Celko
[email protected]