Wednesday 13 July 2011

How to get the running balance from a table with a single SQL query




Table structure for Balance table-

VRNO
VRDT
DRAMT
CRAMT
RNUM
NULL
1-May-10
18108498
19690002
1
SCMEG/00379/2010-2011
1-May-10
277.75
0
2
SCMEG/00380/2010-2011
1-May-10
277.75
0
3
SCMEG/00381/2010-2011
1-May-10
266.63
0
4
SCMEG/00382/2010-2011
1-May-10
277.75
0
5
SCMEG/00378/2010-2011
1-May-10
266.63
0
6
SCMEG/00383/2010-2011
1-May-10
244.5
0
7
SALFC/01129/2010-2011
1-May-10
0
15466.65
8
SALFC/01155/2010-2011
1-May-10
0
11799.98
9
SALFC/01157/2010-2011
1-May-10
0
11799.98
10
SALFC/01158/2010-2011
1-May-10
0
11799.98
11
SALFC/01160/2010-2011
1-May-10
0
11799.98
12

Required result from the query -
Like in a Bank's money transaction output, the RunningBalance column is needed which displays the balance an account holds against that will account transaction.
SELECT N.VRNO,N.VRDT,N.DRAMT,N.CRAMT,SUM(O.BALAMT) AS BALAMT
FROM TABLENAME N
INNER JOIN TABLENAME O ON N.RNUM >= O.RNUM
GROUP BY N.VRNO,N.VRDT,N.DRAMT,N.CRAMT,N.RNUM               
ORDER BY N.RNUM

VRNO
VRDT
DRAMT
CRAMT
BALAMT
NULL
1-May-10
18108498
19690002
-1581504
SCMEG/00379/2010-2011
1-May-10
277.75
0
-1581226
SCMEG/00380/2010-2011
1-May-10
277.75
0
-1580949
SCMEG/00381/2010-2011
1-May-10
266.63
0
-1580682
SCMEG/00382/2010-2011
1-May-10
277.75
0
-1580404
SCMEG/00378/2010-2011
1-May-10
266.63
0
-1580138
SCMEG/00383/2010-2011
1-May-10
244.5
0
-1579893
SALFC/01129/2010-2011
1-May-10
0
15466.65
-1595360
SALFC/01155/2010-2011
1-May-10
0
11799.98
-1607160
SALFC/01157/2010-2011
1-May-10
0
11799.98
-1618960
SALFC/01158/2010-2011
1-May-10
0
11799.98
-1630760
SALFC/01160/2010-2011
1-May-10
0
11799.98
-1642560

The result is achieved by a self join of the Balances table. The first table N returns the complete table output, inner join has condition. The condition N.RNUM >= O.RNUM ' is the most important which gives the result for SUM(O.BALAMT).


Posted By: Palash Paul

No comments:

Post a Comment