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