How to Return Running Product Totals

How to Return Running Product Totals

I want to run a query against the Northwind Order Details table that returns running product totals for each product up to a supplied value. For example, if I supply the limit value of 100, I want to generate the output that Figure 1 shows. In this output, the total quantity of the first four orders of product 1 is 98; the fifth order of product 1 isn't included because the total quantity would exceed the limit value of 100 that I specified.

Also, I want to be able to slightly change the requirement and return all orders for each product up to and including the first order for which the running product total reaches or passes the supplied value. For example, if I supply the value 100, I want to generate the output that Figure 2 shows. These totals are required for various inventory-related purposes.

First, you can write a query that returns the running product totals without filtering the output. For example, the following statement joins the Order Details table to itself. Note the query's JOIN condition:

ON OD1.ProductID = OD2.ProductID AND OD2.OrderID <= OD1.OrderID

Each order for each product in the Order Details instance called OD1 matches an order in the OD2 instance based on the same ProductID and on an OrderID in OD2 that is less than or equal to the OrderID in OD1. If you group the result by OD1.ProductID and OD1.OrderID, you can return the product running total by using the aggregate function SUM(OD2.Quantity) to summarize all the quantities from OD2. In the JOIN operation's result, (which is obtained before the aggregations are calculated), the same order in OD1 appears as many times as you find matches in OD2. The GROUP BY clause includes OD1.ProductID and OD1.OrderID, but it doesn't include OD1.Quantity. According to the ANSI-SQL requirement, to return OD1.Quantity in the result, you have to either include it in the GROUP BY clause or wrap it with an aggregate function. If you decide to wrap it with an aggregate function, you can use the MIN() or MAX() function, because all the OD1.Quantity values are the same in each group. Using these functions is a small trick that lets you pick one quantity value out of all the duplicates. Listing 1 shows the query that returns running product totals.

When you want product totals to add up to a supplied value, you need to add to Listing 1's query a HAVING clause that returns only the rows that have a product running total less than the supplied value. Listing 2 includes the HAVING clause. However, when you want to return all orders for each product up to and including the first order for which the running product total reaches or passes the supplied value, you need to write a more complex query. To simplify the solution, you can first wrap Listing 1's query with a view, which Listing 3 shows. Next, you can write a query against the VProdRunTotals view, which returns all rows whose OrderID is less than or equal to the minimum OrderID that has a running total equal to or greater than the supplied value. Note that you also have to accommodate a situation in which none of the view's rows contain a product running total equal to or greater than the supplied value. Listing 4 shows how you can use the ISNULL() function to address this problem by substituting the OrderID for the NULL return value.