Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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
71062.1056@compuserve.com

DevX Pro
 
Comment and Contribute

 

 

 

 

 


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

 

 

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