Calculating Running Totals

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

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:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as