Home » SQL & PL/SQL » SQL & PL/SQL » Balance amount counting on row level
Balance amount counting on row level [message #269713] Mon, 24 September 2007 05:02 Go to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

Dear all,

I have one query.

i want to calculate balace amount of col. on each rows.

eg

i have following data

sr date lic_on cr dr
1 12-05-07 05454 10000
2 13-05-07 05454 2000
3 13-05-07 05454 1000
4 15-05-07 05455 4000


i want result of balance amount as

sr date lic_on cr dr balance.
1 12-05-07 05454 10000 10000
2 13-05-07 05454 2000 8000
3 13-05-07 05454 1000 7000
4 15-05-07 05455 4000 14000

can any guru tell me how can i calculate the value with one sql query

Regards

Ganesh Jadahv

Re: Balance amount counting on row level [message #269718 is a reply to message #269713] Mon, 24 September 2007 05:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Still didn't you get a chance to look at OraFAQ Forum Guide?

Have you searched for Oracle Analytical Functions(especially LAG)?

By
Vamsi
Re: Balance amount counting on row level [message #269724 is a reply to message #269718] Mon, 24 September 2007 05:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
From Private Messaging
Quote:
Thanxx for replying

but lag function only calculate on previous values

i had tried to take it on previous value of (balace amount) but it didn't work
still you didn't read the Forum Guide.

Seems I was wrong. You may need to use sum and first_value.
with data as (select 8000 m from dual
union all select 2000 from dual
union all select 1000 from dual)
select m
     ,(2 * first_value(m) over(order by m desc)) 
       - sum(m) over(order by m desc)
  from data;
By
Vamsi
Re: Balance amount counting on row level [message #269725 is a reply to message #269713] Mon, 24 September 2007 05:39 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
create table test(sr varchar2(10),entry_date date,
lic_on varchar2(10), dr number, cr number);

insert into test values(1,'12-may-2007','05454',0,10000);

insert into test values(2,'13-may-2007','05454',2000,0);

insert into test values(3,'13-may-2007','05454',1000,0);

insert into test values(4,'15-may-2007','05455',0,4000);

insert into test values(5,'13-may-2007','05454',1000,0);

select sr,entry_date,lic_on,cr, dr, 
( sum(cr) over(PARTITION BY lic_no order by sr RANGE UNBOUNDED PRECEDING) -
sum(dr) over(PARTITION BY lic_no order by sr RANGE UNBOUNDED PRECEDING)) Balance
from test;

SR         ENTRY_DAT LIC_ON             CR         DR    BALANCE
---------- --------- ---------- ---------- ---------- ----------
1          12-MAY-07 05454           10000          0      10000
2          13-MAY-07 05454               0       2000       8000
3          13-MAY-07 05454               0       1000       7000
5          13-MAY-07 05454               0       1000       6000
4          15-MAY-07 05455            4000          0       4000


Re: Balance amount counting on row level [message #269726 is a reply to message #269725] Mon, 24 September 2007 05:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Oops!!!
There were two columns cr and dr. Razz

See the difference, when you use code tags!

By
Vamsi
Re: Balance amount counting on row level [message #269733 is a reply to message #269713] Mon, 24 September 2007 05:53 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I answered the same kind of question several months ago on AskTom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3083286970877#267273900346652341

Regards
Michel
Previous Topic: Error in Running Parallel Queries
Next Topic: viewing package body text
Goto Forum:
  


Current Time: Sat Dec 03 20:00:08 CST 2016

Total time taken to generate the page: 0.04315 seconds