Oracle aggregation function to allocate amount

This should do the trick:

select t1.bag_id     , t1.bag_type     , t1.capacity     , least( t1.capacity -- don't over fill the bag            , greatest( 0 -- don't under fill the bag                      , t2.item_amount  -- to be allocated                      - nvl(sum(t1.capacity) -- less previous allocations                          over (partition by t1.bag_type                                    order by t1.capacity desc                             rows between unbounded preceding and 1 preceding)                           , 0))) Allocated  from t1  join t2    on t2.item_type = t1.bag_type;    BAG_ID B   CAPACITY  ALLOCATED---------- - ---------- ----------         1 A        500        500         2 A        300        300         3 A        100         50         4 B        200        200         5 B        100        100

You are looking for a cumulative sum. Something like this:

select t1.*,       (case when cumecap <= t2.item_amount              then t1.capacity             when cumecap - t1.capacity <= t2.item_amount             then t2.item_amount - (cumecap - t1.capacity)             else 0        end) as allocated_capacityfrom (select t1.*,             sum(t1.capacity) over (partition by bag_type order by bag_id) as cumecap      from t1     ) t1 join     t2     on t1.bag_type = t2.item_type;

assuming allocation in the descendent order of bag capacity

with agg as ( select bag.BAG_ID, bag.BAG_TYPE, bag.CAPACITY,SUM(bag.CAPACITY) over (partition by bag.bag_type order by bag.capacity DESC) agg_capacity,item_amountfrom bag, itemwhere bag.bag_type = item.item_type)select     BAG_ID, BAG_TYPE, CAPACITY,     case when ITEM_AMOUNT >= AGG_CAPACITY then CAPACITY /* Full allocated */    when ITEM_AMOUNT >= AGG_CAPACITY-CAPACITY then  ITEM_AMOUNT - (AGG_CAPACITY-CAPACITY) /* partly allocated */     else 0 end /* not allocated */    as allocated from aggorder by bag_type, capacity desc;    BAG_ID BAG_TYPE   CAPACITY  ALLOCATED   ------ -------- ---------- ----------     1 A               500        500      2 A               300        300      3 A               100         50      4 B               200        200      5 B               100        100 

Note that the order of the allocation is important if you want to minimize the waste capacity and finding an optimal allocation using different orders could be hard.