Home » SQL & PL/SQL » SQL & PL/SQL » cummulative running balance by condition (Oracle 10g)
cummulative running balance by condition [message #580437] Sun, 24 March 2013 07:44 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Is there a way to find running balance for accounts, suppose i have two tables.one for transaction and one summary of balance.
i want to have the closing balance based on transactions subtracted or added based on credit and debit for example.it will add to the running total if its debit and subtract if its credit and final output is the balance.


create table acnt_trans ( acnt_code varchar2(12),trans_date date,debit_amt number,credit_amt number);

insert into acnt_trans values ('1001','02-FEB-2013',200,0);
insert into acnt_trans values ('1001','03-FEB-2013',0,100);
insert into acnt_trans values ('1001','04-FEB-2013',50,0);

commit;

create table acnt_bal (b_acnt_code varchar2(12),b_acnt_yrmth varchar2(12),b_acnt_bal number);

insert into acnt_bal values ('1001','201302',3000);

commit;

--I want the output as below.if i run the report as of '04-feb-2013'
trans_date  ,  acnt_code , debit_amt,credit_amt , balance
02-feb-2013    1001           200         0        3200
03-feb-2013    1001             0       100        3100
04-feb-2013    1001            50         0        3150  -- this is the closing balance.




Re: cummulative running balance by condition [message #580440 is a reply to message #580437] Sun, 24 March 2013 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way to find running balance for accounts


It's easy just use SUM in its analytic form.
Refer to the function in Database SQL Reference, there is an example of it.

Note that your test case is not valid:
SQL> create table acnt_trans ( acnt_code varchar2(12),trans_date date,debit_amt number,credit_amt number);

Table created.

SQL> insert into acnt_trans values ('1001','02-FEB-2013',200,0);
insert into acnt_trans values ('1001','02-FEB-2013',200,0)
                                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

When you use a constant for a date ALWAYS use TO_DATE with a format to convert it to a date constant.
And better use numbers than names, we do not all use the same language (or specify the language in the TO_DATE function).

Regards
Michel
Re: cummulative running balance by condition [message #580458 is a reply to message #580440] Sun, 24 March 2013 23:39 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi michel , thanks for the mail, i went throgh that reference and managed to get the results but i also need to consider the opening balance from other table which i am not getting.


drop table acnt_trans

create table acnt_trans ( acnt_code varchar2(12),trans_date date,debit_amt number,credit_amt number);

insert into acnt_trans values ('1001',to_date('01/02/2013','DD/MM/RRRR'),200,0);
insert into acnt_trans values ('1001',to_date('02/02/2013','DD/MM/RRRR'),0,100);
insert into acnt_trans values ('1001',to_date('04/02/2013','DD/MM/RRRR'),50,0);

create table acnt_bal (b_acnt_code varchar2(12),b_acnt_yrmth varchar2(12),b_acnt_bal number);

insert into acnt_bal values ('1001','201302',3000);

select * from acnt_bal


SELECT acnt_code,trans_date,decode(sign(debit_amt) - (credit_amt),1,1 ,-1) SIG,
 decode(sign(debit_amt) - (credit_amt),1,DEBIT_AMT ,-CREDIT_AMT) AMT ,b_acnt_bal,
 SUM( decode(sign(debit_amt) - (credit_amt),1,DEBIT_AMT ,-CREDIT_AMT)) OVER (PARTITION BY ACNT_CODE ORDER BY TRANS_DATE )    CUMMULATIVE_BALCE
 FROM ACNT_TRANS ,acnt_bal
 where acnt_code = b_acnt_code

--What i am getting now

ACNT_CODE	TRANS_DATE	SIG	 AMT	B_ACNT_BAL	CUMMULATIVE_BALCE
1001	        02/01/2013 	 1	 200	  3000	             200
1001	        02/02/2013      -1	-100	  3000	             100
1001	        02/04/2013 	 1	  50	  3000	             150
					
--Required output is as below or what i want.					
					
ACNT_CODE	TRANS_DATE	SIG	 AMT	B_ACNT_BAL	CUMMULATIVE_BALCE
1001	         02/01/2013 	 1	 200	 3000	             3200
1001	         02/02/2013 	-1	-100	 3000	             3100
1001	         02/04/2013 	 1	  50	 3000	             3150






Re: cummulative running balance by condition [message #580463 is a reply to message #580458] Mon, 25 March 2013 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58855
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What prevents you from adding "b_acnt_bal" and "CUMMULATIVE_BALCE" formula? just replace "," between the two fields by "+".

Regards
Michel
Re: cummulative running balance by condition [message #580466 is a reply to message #580463] Mon, 25 March 2013 01:41 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks a lot michel , i did the same thing and i got the results.
Previous Topic: Function to insert
Next Topic: creating a package
Goto Forum:
  


Current Time: Thu Aug 21 11:58:45 CDT 2014

Total time taken to generate the page: 0.06544 seconds