Running Total or Cumulative Balance in SQL Server

At first we Create a table named TAB1

USE tempdb

CREATE TABLE TAB1 (ID INT, InQty INT, OutQty INT)

GO

Then insert data into TAB1

INSERT INTO TAB1 (ID,InQty,OutQty)

SELECT 1,10,0 UNION ALL

SELECT 2,20,10 UNION ALL

SELECT 3,10,20 UNION ALL

SELECT 4,0,0 UNION ALL

SELECT 5,0,10 UNION ALL

SELECT 6,10,20 UNION ALL

SELECT 7,30,10

GO

Now the Output

SELECT * FROM TAB1

Here the Running TOTAL Calculation OR Cumulative Balance Query is shown for SQL Server 2008 R2 or Earlier Versions.

SELECT *,(SELECT SUM(InQty-OutQty)

              FROM TAB1 T2

              WHERE T2.ID <= T1.ID) AS BalnQty

FROM TAB1 T1

And this is for SQL Server 2012 or Later Versions.

SELECT * ,

SUM(InQty-OutQty) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING) AS BalnQty

FROM TAB1

RESULT

If there is any other better option, Please share it here.

Thank You,
Arka Gupta.

http://arkaguptablog.blogspot.in/