Home » SQL & PL/SQL » SQL & PL/SQL » find recurring balance (9.2.0.5 enterprise on linux)
find recurring balance [message #298507] Wed, 06 February 2008 04:38 Go to next message
laksha
Messages: 42
Registered: June 2006
Member
inv_id	date	    inv_amt	amt_paid	balance
----------------------------------------------------------
1	1-Jan-08	400	50	         350
1	2-Jan-08	400	100	         250
1	3-Jan-08	400	50	         200
1	4-Jan-08	400	150	          50
2	1-Jan-08	500	100	         400
2	2-Jan-08	500	50	         350
2	3-Jan-08	500	200	         150
2	4-Jan-08	500	100	          50

The needed output is "balance" column which is previous "balance" minus "amt_paid" for each row.
Re: find recurring balance [message #298523 is a reply to message #298507] Wed, 06 February 2008 04:55 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try SUM ANALYTIC FUNCTION and deduct from Investment amount

Thumbs Up
Rajuvan
Re: find recurring balance [message #298525 is a reply to message #298507] Wed, 06 February 2008 04:57 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
one way would be using SUM() analytical function.something like

select ....sum(amt_paid) over (partition clause...). then subtract this sum(amt_paid) by inv_amt.


regards,
Re: find recurring balance [message #298546 is a reply to message #298507] Wed, 06 February 2008 05:54 Go to previous message
laksha
Messages: 42
Registered: June 2006
Member
Thanks for the solution.
Previous Topic: summing up values...
Next Topic: Array in PLSQL
Goto Forum:
  


Current Time: Sat Dec 03 18:19:45 CST 2016

Total time taken to generate the page: 0.06799 seconds