How do i calculate running balance in SQL

Question

I am using embedded derby database and I want to add running balance column by calculating debit and credit amounts so please tell me solution code and image are also available below

(as you can see in image balance total is not accurate)

SELECT 
V_DATE,FLAG,V_NUM,V_NARATION,
sum(VDTL.DR_AMOUNT) AS DEBIT,
sum(VDTL.CR_AMOUNT) AS CRIDIT,
sum(dr_amount)-sum(cr_amount) as total
FROM VOUCHARDETAIL AS VDTL
INNER JOIN VOUCHARMASTER AS VMST
ON VDTL.DTL_NUM =VMST.MST_NUM
WHERE (V_DATE BETWEEN '12-03-2017' AND '14-03-2017') AND (AC_CODE=60030002) 
GROUP BY
V_DATE,FLAG,V_NUM,V_NARATION
ORDER BY
V_DATE

enter image description here

Why you post same question [http://stackoverflow.com/questions/42897968/how-to-calculate-running-balance-in-sql](http://stackoverflow.com/questions/42897968/how-to-calculate-running-balance-in-sql) ? — 鄭有維, Mar 21 '17 at 06:42

score 0 · Answer 1 · answered Mar 21 '17 at 06:44

This could be a possible solution.

SELECT V_DATE,
       FLAG,
       V_NUM,
       V_NARATION,
       SUM(VDTL.DR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DEBIT,
       SUM(VDTL.CR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CREDIT
(SUM(VDTL.DR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)-SUM(VDTL.CR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) as TOTAL
FROM VOUCHARDETAIL AS VDTL
INNER JOIN VOUCHARMASTER AS VMST
ON VDTL.DTL_NUM =VMST.MST_NUM
WHERE (V_DATE BETWEEN '12-03-2017' AND '14-03-2017') AND (AC_CODE=60030002) 
ORDER BY V_DATE

score 0 · Answer 2 · answered Mar 21 '17 at 06:51

Try This )

 select sel.*, sel.total + LAG(sel.total,1,0) over (order by Sel.V_DATE) as running_balance from (SELECT 
    V_DATE,FLAG,V_NUM,V_NARATION,
    sum(VDTL.DR_AMOUNT) AS DEBIT,
    sum(VDTL.CR_AMOUNT) AS CRIDIT,
    sum(dr_amount)-sum(cr_amount) as total,
    FROM VOUCHARDETAIL AS VDTL
    INNER JOIN VOUCHARMASTER AS VMST
    ON VDTL.DTL_NUM =VMST.MST_NUM
    WHERE (V_DATE BETWEEN '12-03-2017' AND '14-03-2017') AND (AC_CODE=60030002) 
    GROUP BY
    V_DATE,FLAG,V_NUM,V_NARATION
    ORDER BY
    V_DATE) Sel
    order by Sel.V_DATE

How do i calculate running balance in SQL

2 Answers2