Calculating Running Totals

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]

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: