CODEWITHSUNDEEP
phpplsql
usman
usman

Reputation: 135

How to make debit and credit formula

i want to sum all debit and subtract credit from debit amount line by line. like sum debit amount from top two rows and subtract credit from row 3,4 and show in ttl blnc column but the below result not correct it just subtract credit from debit

enter image description here

i need this result

debit   Credit  Running Total
144       0       144
264       0       408
0        264      144
0        441     -297
2464      0       2167
144       0       2311
0      27306      -24995
264       0       -24731

this is my code 

  result number:=0;
begin
  result:= result +  debit - credit;
  return result;

Upvotes: 1

Views: 432

Answers (2)

Christian Palmer
Christian Palmer

Reputation: 1302

Assuming you want to see the running total row-by-row, I'd use the SUM() analytics function. 

SELECT 
 xti.dr_amount
,xti.cr_amount   
,SUM(dr_amount-cr_amount) OVER (ORDER BY xti.invoice_num)  running_total
FROM
 xxcjp_test_invoices       xti
ORDER BY
 xti.invoice_num
;

Use the following to create the test data

CREATE TABLE XXCJP_TEST_INVOICES
(invoice_num    VARCHAR2(10)
,dr_amount      NUMBER
,cr_amount      NUMBER
)
;

INSERT INTO XXCJP_TEST_INVOICES VALUES ('A001',144,0) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A002',264,0) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A003',0,264) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A004',0,441) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A005',2464,0) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A006',144,0) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A007',0,27306) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A008',264,0) ;
INSERT INTO XXCJP_TEST_INVOICES VALUES ('A009',55500,0) ;

Upvotes: 0

Hafiz Usman aftab
Hafiz Usman aftab

Reputation: 310

use this formulla 

RETURN(NVL(:CF_ttl_blnc,0)+NVL(:debit,0)-NVL(:credit,0));

Upvotes: 1

Related Questions