Oracle query credit,debit and balance calculate

SELECT NARRATION,PARTICULARS,TO_CHAR(VOUCHER_DATE,'dd-Mon-yyyy') VOUCHER_DATE, nvl(CREDIT,0) RECEIPT, NVL(DEBIT,0) PAYMENT,
NVL(CREDIT,0) - nvl(DEBIT,0) BALANCE,SUM (NVL(CREDIT,0) - nvl(DEBIT,0)) OVER (PARTITION BY VOUCHER_DATE ORDER BY ROWNUM) CUM_BAL
FROM EGMSDBA.VOUCHER_DETAILS
WHERE NARRATION IN ('Cash Receipt','Cash Payment','Cash Deposit') AND VOUCHER_DATE BETWEEN '01-Apr-2016' AND '01-Apr-2017'

ORDER BY VOUCHER_DATE,VOUCHER_SL_NO;

NARRATION PARTICULARS DATE RECEIPT PAYMENT BALANCE

------------------ ----------------------- ----------- ----------------- ----------------- ---------------------

Cash Receipt    Aroma Agrotech Pvt Ltd    21-Feb-2017    100000    0            100000
Cash Payment    Dalmir S/O Surat Singh     21-Feb-2017 0              550       94500
Cash Payment    Dilbag S/O Ramkishan   21-Feb-2017      0             2500      92000
Cash Payment    Dilbagh S/O Sewa            21-Feb-2017       0            1700      90300
Cash Deposit   Canara Banks                   21-Feb-2017        0           10000    80300
Cash Receipt      Aroma Agrotech Pvt Ltd 22-Feb-2017       20000       0      20000
Cash Receipt       Bayer India Ltd                22-Feb-2017      15500        0    35500
Cash Receipt       Haryana Ware House     22-Feb-2017      14000       0         49500

I WANT TO NEED THIS RESULT SHOW BELOW

NARRATION PARTICULARS DATE RECEIPT PAYMENT BALANCE

------------------ ----------------------- ----------- ----------------- ----------- ------------

Cash Receipt    Aroma Agrotech Pvt Ltd       21-Feb-2017 100000     0             100000
Cash Payment    Dalmir S/O Surat Singh       21-Feb-2017 0                550          94500
Cash Payment    Dilbag S/O Ramkishan       21-Feb-2017    0                2500       92000
Cash Payment    Dilbagh S/O Sewa             21-Feb-2017      0                1700       90300
Cash Deposit     Canara Banks                    21-Feb-2017       0                10000     80300
Cash Receipt      Aroma Agrotech Pvt Ltd    22-Feb-2017      20000       0             100300
Cash Receipt       Bayer India Ltd                 22-Feb-2017       15500        0             115800
Cash Receipt       Haryana Ware House       22-Feb-2017      14000        0             129800

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jitendra Shekhawat9y ago
https://community.oracle.com/thread/3961435

https://docs.oracle.com/cd/E16582_01/doc.91/e15112/reconciledandcamts.htm#EOAGA01083

https://www.club-oracle.com/threads/how-to-calculate-debit-credit-balance-sdate-for-sql-query.52288/
0