RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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