Click here to Skip to main content
Click here to Skip to main content

Applying Running Total to a Result set

By , 8 May 2012
Rate this:
Please Sign up or sign in to vote.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Chamila Ranasinghe
Software Developer
Sri Lanka Sri Lanka
No Biography provided

Comments and Discussions

 
QuestionSingle query? PinmvpMika Wendelius9-May-12 7:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 9 May 2012
Article Copyright 2012 by Chamila Ranasinghe
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid