manoj kumar

manoj kumar

  • 1.4k
  • 225
  • 20.3k

Oracle query credit,debit and balance calculate

Apr 10 2017 7:47 AM
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
 

Answers (1)