Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 25, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Calculating Running Totals

[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:
    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:
Joe Celko

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date