clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591646 fiddles created (45708 in the last week).

create table t as select 1 as id, 'name_a' as name, 3 as quantity, 5 as maxquantity from dual union all select 2 as id, 'name_a' as name, 1 as quantity, 5 as maxquantity from dual union all select 3 as id, 'name_a' as name, 3 as quantity, 5 as maxquantity from dual union all select 4 as id, 'name_a' as name, 5 as quantity, 5 as maxquantity from dual union all select 5 as id, 'name_b' as name, 7 as quantity, 4 as maxquantity from dual union all select 6 as id, 'name_b' as name, 2 as quantity, 4 as maxquantity from dual
6 rows affected
 hidden batch(es)


with cte (id, name, quantity, maxquantity, n) as ( select id, name, quantity, maxquantity, 1 as n from t union all select id, name, quantity, maxquantity, n + 1 from cte where n < quantity ) select id, name, quantity, maxquantity, count(*) as number_in_bin, ceil(bin_counter / maxquantity) as bin_number from (select cte.*, row_number() over (partition by name order by id, n) as bin_counter from cte ) cte group by id, name, quantity, maxquantity, ceil(bin_counter / maxquantity) order by id, bin_number
ID NAME QUANTITY MAXQUANTITY NUMBER_IN_BIN BIN_NUMBER
1 name_a 3 5 3 1
2 name_a 1 5 1 1
3 name_a 3 5 1 1
3 name_a 3 5 2 2
4 name_a 5 5 3 2
4 name_a 5 5 2 3
5 name_b 7 4 4 1
5 name_b 7 4 3 2
6 name_b 2 4 1 2
6 name_b 2 4 1 3
 hidden batch(es)


with n as ( select 0 as n from dual union all select 1 as n from dual union all select 2 as n from dual ) select t.*, bin_start + n as final_bin, (1 + least(end_quantity - (bin_start + n - 1) * maxquantity, maxquantity) - greatest(start_quantity - (bin_start + n - 1) * maxquantity, 0) ) as quantity_in_bin from (select t.*, ceil(start_quantity / maxquantity) as bin_start, ceil(end_quantity / maxquantity) as bin_end from (select t.*, sum(quantity) over (partition by name order by id) - quantity + 1 as start_quantity, sum(quantity) over (partition by name order by id) as end_quantity from t ) t ) t join n on n + bin_start <= bin_end order by id, final_bin
ID NAME QUANTITY MAXQUANTITY START_QUANTITY END_QUANTITY BIN_START BIN_END FINAL_BIN QUANTITY_IN_BIN
1 name_a 3 5 1 3 1 1 1 3
2 name_a 1 5 4 4 1 1 1 1
3 name_a 3 5 5 7 1 2 1 1
3 name_a 3 5 5 7 1 2 2 3
4 name_a 5 5 8 12 2 3 2 3
4 name_a 5 5 8 12 2 3 3 3
5 name_b 7 4 1 7 1 2 1 4
5 name_b 7 4 1 7 1 2 2 4
6 name_b 2 4 8 9 2 3 2 1
6 name_b 2 4 8 9 2 3 3 2
 hidden batch(es)