Hi,
I want to fill the buckets as shown in the desired result in the priority of priorities table in multiple of item quantity given in volume table.
In the desired result, if you see the iter1 column tells, how quantity is decreased first and then again increased to fill the buckets in the result column, generating an additional row when there is the leftover quantity that is not in the multiple with status N.
create table priorities (bucket, priority) as
select 'A', 1 from dual union all
select 'B', 2 from dual union all
select 'C', 3 from dual union all
select 'D', 4 from dual union all
select 'E', 5 from dual
;
create table volumes (item, quantity) as
select 'P1', 10 from dual union all
select 'P2', 5 from dual union all
select 'P3', 10 from dual
;
create table data (bucket, item, present_qty) as
select 'A', 'P1', 11 from dual union all
select 'B', 'P1', 15 from dual union all
select 'C', 'P1', 9 from dual union all
select 'D', 'P1', 12 from dual union all
select 'E', 'P1', 8 from dual union all
select 'A', 'P2', 11 from dual union all
select 'B', 'P2', 15 from dual union all
select 'C', 'P2', 9 from dual union all
select 'D', 'P2', 12 from dual union all
select 'E', 'P2', 8 from dual union all
select 'A', 'P3', 9 from dual union all
select 'B', 'P3', 9 from dual union all
select 'C', 'P3', 9 from dual union all
select 'D', 'P3', 9 from dual union all
select 'E', 'P3', 9 from dual
;
BUCKET ITEM START_QTY ITER1 RESULT_QTY STATUS
------ ---- ---------- ---------- ---------- ------
A P1 11 10 10 Y
B P1 15 10 10 Y
C P1 9 9 10 Y
D P1 12 10 10 Y
E P1 8 8 10 Y
P1 8 5 N
A P2 11 10 15 Y
B P2 15 15 20 Y
C P2 9 5 5 Y
D P2 12 10 10 Y
E P2 8 5 5 Y
A P3 9 9 10 Y
B P3 9 9 10 Y
C P3 9 9 10 Y
D P3 9 9 10 Y
E P3 9 9 0 N
P3 0 5 N