Applying Running Total to a Result set

• Download source - 1.1 KB

Introduction

Let's have a quick look at how to add a running total column to a result set.

Background

Here there are two tables called GRN and Invoice. GRN contains all the received products while Invoice contains issued products.

Let's insert the concatenated result set into a memory table. Here invoice Qty is inserted as (-) value. Remember to order the result by date before insert. Declare a variable to hold the running total and update running total column.

Using the Code

--- Declare a memory table
DECLARE @ProductList TABLE
(
	ProductID	INT,
	[DATE]		DATE,
	Qty		INT,
	RuningTotal     INT	
)			
 
---- Concatenate the result from GRN and Invoice table
---- Set invoice Qty as (-) value
---- Insert result set in the memory table ordered by date
INSERT INTO @ProductList
SELECT ProductID, [DATE], QTY, 0 AS RuningTotal 
FROM
 
(SELECT ProductID, [DATE], QTY, 0 AS RuningTotal FROM GRN 
UNION ALL
SELECT ProductID, [DATE], - (QTY), 0 AS RuningTotal FROM Invoice 
)AS A
ORDER BY A.[Date]
 
---- Declare running balance variable
DECLARE @RunningBalance INT
SET @RunningBalance=0
 
---- Update the running total column
UPDATE @ProductList
SET  @RunningBalance = RuningTotal = @RunningBalance + (Qty)     
FROM @ProductList
 
---- Result
SELECT * FROM @ProductList

The result will appear as follows:

History

• May 09, 2012: Article created