Let's have a quick look at how to add a running total column to a result set.
Here there are two tables called
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 @ProductList TABLE
INSERT INTO @ProductList
SELECT ProductID, [DATE], QTY, 0 AS RuningTotal
(SELECT ProductID, [DATE], QTY, 0 AS RuningTotal FROM GRN
SELECT ProductID, [DATE], - (QTY), 0 AS RuningTotal FROM Invoice
ORDER BY A.[Date]
DECLARE @RunningBalance INT
SET @RunningBalance = RuningTotal = @RunningBalance + (Qty)
SELECT * FROM @ProductList
The result will appear as follows:
- May 09, 2012: Article created