Running Total in SQL Server

Hi, Now we discuss about Running Total in SQL Server. It's very much used for Closing Balance. Here we see an example of a banking system.

STEP 1:  Create a Table named ACC_TAB

CREATE TABLE ACC_TAB

(Id INT,

 Account VARCHAR(10),

 Debit NUMERIC(18,2),

 Credit NUMERIC(18,2))

STEP 2:  Insert data into ACC_TAB table.

INSERT INTO ACC_TAB

SELECT 1,'Acc_1',100.76,0 UNION ALL

SELECT 1,'Acc_2',54.98,0 UNION ALL

SELECT 1,'Acc_3',0,43.89 UNION ALL

SELECT 2,'Acc_2',0,60.17 UNION ALL

SELECT 2,'Acc_3',135.76,0 UNION ALL

SELECT 3,'Acc_3',18.54,0 UNION ALL

SELECT 2,'Acc_1',0,40.98 UNION ALL

SELECT 3,'Acc_1',0,20.76 UNION ALL

SELECT 4,'Acc_1',90.72,0 UNION ALL

SELECT 3,'Acc_2',60.76,0 UNION ALL

SELECT 4,'Acc_2',0,68.04 UNION ALL

SELECT 5,'Acc_1',86.76,0 UNION ALL

SELECT 4,'Acc_3',0,70.12 

STEP 3:  Select ACC_TAB Table 

SELECT * FROM ACC_TAB ORDER BY Account,Id

STEP 4:  Now calculate the Running Total.

SELECT T1.*,

       (SELECT SUM(Debit-Credit)

        FROM ACC_TAB T2

        WHERE T2.Id <= T1.Id

        AND T2.Account = T1.Account

       ) AS [Balance(Dr-Cr)]

FROM ACC_TAB T1

ORDER BY T1.Account,T1.Id

STEP 5:  See the Result.

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,
Arka Gupta.

http://arkaguptablog.blogspot.in/

https://plus.google.com/+ArkaGupta-arkagupta/posts