Home » SQL & PL/SQL » SQL & PL/SQL » Accumulate sum until reaching and *exact* required value ?
Accumulate sum until reaching and *exact* required value ? [message #215710] Tue, 23 January 2007 11:04 Go to next message
solmyr72
Messages: 3
Registered: January 2007
Junior Member
Hi,

Could anyone please tell if oracle SQL supports the following requirement ?
it's easy to do in PL/SQL, but I was aiming for pure SQL...

The requirement is basically:
"Accumulate sum until reaching an *exact* required sum (taking part of the last row, if required)".

Example: suppose we have a table of DONERS, for each doner there's the maximun sum he/she is willing to donate.

DONER table:
------------------------------------------------------------
DONER_ID .... MAX_DONATION
------------------------------------------------------------
1 ................... 1000
2 ................... 500
3 ................... 700


Also, we have a single-row table holding the required sum of donations we wish to collect:

REQUIRED_DONATION table [single row]
------------------------------------------------------------
1400
------------------------------------------------------------

Given those, i'd like to generate a view , listing how much to charge from each doner (assuming we prefer to first charge users with small IDs):

CHARES_VIEW [how do you generate this ?]
------------------------------------------------------------
DONER_ID .... CHARGE
------------------------------------------------------------
1 ................... 1000
2 ................... 400 [stop here, having reached the required 1400]


Thanks very much in advance.
Re: Accumulate sum until reaching and *exact* required value ? [message #215717 is a reply to message #215710] Tue, 23 January 2007 11:26 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You should be able to do it by using the sum over (partition).
You would need some sort of date column with which to order the donations. Something like the following should give you the basis of what you need:

create table donor (donor_Id number, amount number, date_donated date);
   
insert into donor values (1, 700, trunc(sysdate));
insert into donor values (1, 500, trunc(sysdate)-1);
insert into donor values (1, 600, trunc(sysdate)-2);
insert into donor values (1, 400, trunc(sysdate)-3);
   
create table req_donation (donor_id number, req_amount number);
   
insert into req_donation values (1, 1400);
   
select donor_id, req_amount, max_donation
from
  (select d.donor_id, r.req_amount, sum(d.amount) over   
      (partition by d.donor_id order by d.date_donated) as max_donation
   from donor D, req_donation r
   where d.donor_id = r.donor_id)
   where max_donation < req_amount;
Re: Accumulate sum until reaching and *exact* required value ? [message #221936 is a reply to message #215717] Thu, 01 March 2007 03:10 Go to previous message
solmyr72
Messages: 3
Registered: January 2007
Junior Member
Thank you very much.
This helped !
Previous Topic: how to round off figure
Next Topic: Query Help with Large Table
Goto Forum:
  


Current Time: Wed Dec 07 14:30:57 CST 2016

Total time taken to generate the page: 0.07136 seconds