RKL eSolutions Blog Trends and Insights

Solve Running Total Problems with MS SQL

KEY LEARNING: If you are using SQL SERVER 2012 or higher, when calculating running total values or variations, use Windowing Aggregate Functions as they are much much more performant than other methods.

SQL

From time to time I run across running total style problems when working with Sage 500. These include showing the Customers running total across invoices and credit memos or applying unapplied payments across open invoices, using the earliest transaction date. One interesting variation is the allocation of on hand inventory to sales orders, by the sales order date, ensuring that the earliest sales order will be shipped first.

Prior to 2012, running total problems could be solved with a number of different approaches:

  • Correlated Sub Query
  • Cursor
  • Quirky Update
  • Row Based Loop

I had tended to go to the Quirky Update approach as this seemed to be the most performant. However I have stopped using that approach because I encountered in instance on a poorly performing SQL Server where the method was non-deterministic.

That has left me with more row by row approaches which always make me think there has got to be something better.

Step in the Microsoft SQL Server team, who extended windowing functions and released them in SQL Server 2012. You’ll be familiar with other windowing functions like Row_Number, DENSE_RANK, RANK, NTILE using the OVER (PARTION BY). In SQL Server 2012, they aggregate window functions, which allow you to specify a partition window, and what rows in the partition to include in the aggregate. For running totals you would specify the first row in the window and all the rows up to the current row.

I wanted to compare the performance of a cursor based inventory allocation to a windows function based allocation. The difference was significant. On a database with 59,171 open sales order lines, the cursor based script took 138 seconds to run. The comparable windowing function took 2 seconds to run.

An article by Aaron Bertrand does a similar study, but uses 7 different methods for calculating running totals.

As soon as you can move to SQL 2012, put the windowing method into practice where appropriate. Your users will see significant improvement where they come into play.

Here is my example code for both the windows function and the cursor based queries.

/*

WINDOWING BASED SOLUTION

*/

 

DECLARE @StartDateTime DATETIME;

DECLARE @EndDateTime DATETIME;

 

SET @StartDateTime = GETDATE();

 

SELECT runtot.WhseKey, runtot.ItemKey, runtot.CreateDate, runtot.SOLineKey, runtot.QtyOpenToShip, runtot.RunningTotal, qoh.QtyOnHand,

QtyAllocated = CASE

WHEN qoh.QtyOnHand >= runtot.RunningTotal THEN runtot.QtyOpenToShip

WHEN qoh.QtyOnHand - runtot.RunningTotal + runtot.QtyOpenToShip >= 0 THEN qoh.QtyOnHand - runtot.RunningTotal + runtot.QtyOpenToShip

ELSE 0 END

FROM (

SELECT sold.WhseKey, sol.ItemKey, so.CreateDate, sol.SOLineKey, sold.QtyOpenToShip,

SUM(QtyOpenToShip) OVER (PARTITION BY  sold.WhseKey, sol.ItemKey ORDER BY sold.WhseKey, sol.ItemKey, so.CreateDate RANGE UNBOUNDED PRECEDING) [RunningTotal]

FROM tsoSalesOrder so WITH (NOLOCK)

JOIN tsoSOLine sol WITH (NOLOCK)

ON so.SOKey = sol.SOKey

JOIN tsoSOLineDist sold WITH (NOLOCK)

ON sol.SOLineKey = sold.SOLineKey

WHERE sold.WhseKey IS NOT NULL

AND sold.QtyOpenToShip > 0

AND so.Status = 1 --Open

AND sol.Status = 1 --Open

) runtot

JOIN (

SELECT  wb.WhseKey, wbi.ItemKey, SUM(wbi.QtyOnHand) QtyOnHand

FROM   timWhseBinInvt               wbi WITH (NOLOCK)

JOIN timWhseBin wb     WITH (NOLOCK)

ON wbi.WhseBinKey = wb.WhseBinKey

GROUP BY wb.WhseKey, wbi.ItemKey

HAVING  SUM(wbi.QtyOnHand) > 0

) qoh

ON runtot.WhseKey = qoh.WhseKey AND runtot.ItemKey = qoh.ItemKey

ORDER BY runtot.WhseKey, runtot.ItemKey, runtot.CreateDate, runtot.SOLineKey;

 

SET @EndDateTime = GETDATE();

 

SELECT DATEDIFF(MICROSECOND, @StartDateTime, @EndDateTime) [Duration_MCS];

 

/*

CURSOR BASED SOLUTION

*/

DECLARE @StartDateTime DATETIME;

DECLARE @EndDateTime DATETIME;

 

SET @StartDateTime = GETDATE();

 

IF OBJECT_ID('tempdb..#SOLineAllocations') IS NOT NULL DROP TABLE #SOLineAllocations;

 

 

DECLARE @WhseKey INT,

@ItemKey INT,

@QtyRemaining DECIMAL(16,8),

@SOLineKey INT,

@QtyOpenToShip DECIMAL(16,8),

@QtyOnHand DECIMAL(16,8),

@QtyAllocated DECIMAL(16,8),

@QtyAllocatedThisLine DECIMAL(16,8);

 

CREATE TABLE #SOLineAllocations

(

WhseKey INT NOT NULL,

ItemKey INT NOT NULL,

CreateDate DATETIME NOT NULL,

SOLineKey INT,

QtyOpenToShip DECIMAL(16,8) NOT NULL,

QtyAllocated DECIMAL(16,8) NOT NULL DEFAULT(0)

);

 

CREATE CLUSTERED INDEX idx_SOLineAllocations_WhseKey_ItemKey_CreateDate_SOLineKey ON #SOLineAllocations (WhseKey, ItemKey, CreateDate, SOLineKey);

 

INSERT INTO #SOLineAllocations (WhseKey, ItemKey, CreateDate, SOLineKey, QtyOpenToShip)

SELECT sold.WhseKey, sol.ItemKey, so.CreateDate, sol.SOLineKey, sold.QtyOpenToShip

FROM tsoSalesOrder so WITH (NOLOCK)

JOIN tsoSOLine sol WITH (NOLOCK)

ON so.SOKey = sol.SOKey

JOIN tsoSOLineDist sold WITH (NOLOCK)

ON sol.SOLineKey = sold.SOLineKey

WHERE WhseKey IS NOT NULL

AND QtyOpenToShip > 0

AND so.Status = 1 --Open

AND sol.Status = 1 --Open

ORDER BY sold.WhseKey, sol.ItemKey, so.CreateDate, sol.SOLineKey;

 

--Simplification for example

DECLARE qty_cursor CURSOR

LOCAL STATIC FORWARD_ONLY READ_ONLY

FOR

SELECT  wb.WhseKey, wbi.ItemKey, SUM(wbi.QtyOnHand) QtyOnHand

FROM   timWhseBinInvt               wbi WITH (NOLOCK)

JOIN timWhseBin wb     WITH (NOLOCK)

ON wbi.WhseBinKey = wb.WhseBinKey

JOIN (

SELECT DISTINCT WhseKey, ItemKey FROM #SOLineAllocations

) wrk

ON wb.WhseKey = wrk.WhseKey AND wbi.ItemKey = wrk.ItemKey

GROUP BY wb.WhseKey, wbi.ItemKey

HAVING  SUM(wbi.QtyOnHand) > 0;

 

OPEN qty_cursor;

 

FETCH NEXT FROM qty_cursor INTO @WhseKey, @ItemKey, @QtyOnHand;

 

WHILE @@Fetch_Status = 0

BEGIN

 

SET @QtyAllocated = 0;

 

DECLARE alloc_cursor CURSOR

FOR

SELECT SOLineKey, QtyOpenToShip

FROM #SOLineAllocations

WHERE WhseKey = @WhseKey

AND ItemKey = @ItemKey

ORDER BY CreateDate;

 

OPEN alloc_cursor;

 

FETCH NEXT FROM alloc_cursor INTO @SOLineKey, @QtyOpenToShip;

 

WHILE  @@Fetch_Status = 0

BEGIN

 

IF @QtyOnHand - (@QtyAllocated + @QtyOpenToShip) >= 0

SET @QtyAllocatedThisLine = @QtyOpenToShip

ELSE IF @QtyOnHand - @QtyAllocated >= 0

SET @QtyAllocatedThisLine = @QtyOnHand - @QtyAllocated

ELSE

SET @QtyAllocatedThisLine = 0

 

UPDATE #SOLineAllocations

SET QtyAllocated = @QtyAllocatedThisLine

WHERE SOLineKey = @SOLineKey

 

SET @QtyAllocated = @QtyAllocated + @QtyAllocatedThisLine

 

IF @QtyAllocatedThisLine <= 0

BREAK;

 

FETCH NEXT FROM alloc_cursor INTO @SOLineKey, @QtyOpenToShip;

END

 

CLOSE alloc_cursor;

DEALLOCATE alloc_cursor;

 

FETCH NEXT FROM qty_cursor INTO @WhseKey, @ItemKey, @QtyOnHand;

END

 

CLOSE qty_cursor;

DEALLOCATE qty_cursor;

 

SELECT * FROM #SOLineAllocations ORDER BY WhseKey, ItemKey, CreateDate;

 

SET @EndDateTime = GETDATE();

 

SELECT DATEDIFF(MICROSECOND, @StartDateTime, @EndDateTime) [Duration_MCS];

John Puttman

Written by John Puttman