I have a scenario as below wherein in the table test_cpy I have multiples units which are distributed across different walls in a store, I need to construct a big box combining these with a maximum number of 20 units per big box.
I need to go in an order working out the combinations from the lowest distribution_info value to the highest i.e. 0 to 9 in this case, it is very much possible that I will not have units in distributions in between.
I have to start combining using this logic if my lowest is 0 and highest is 9 which means total number of distributions = 10.
No of combinations to check = Total Combinations - 1 = 9 (10 -1)
Combinations to check :-
2 digit consecutive combinations - (0,1),(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,8),(8,9),(9,10)
3 digit consecutive combinations - (0,1,2),(1,2,3),(2,3,4),(3,4,5),(4,5,6),(5,6,7),(6,7,8),(7,8,9),(8,9,10)
4 digit consecutive combinations - (0,1,2,3),(1,2,3,4),(2,3,4,5),(3,4,5,6),(4,5,6,7),(5,6,7,8),(6,7,8,9),(7,8,9,10)
Like wise till 9 Digit combinations - (0,1,2,3,4,5,6,7,8,9)
Objective is to achieve the big box by using minimum combinations.
Whichever combinations yield a box, i need to assign them a number. Any help on the logic/ queries to work the combinations out is appreciated, thanks.
DDL
create table test_cpy ( unit_no varchar2(50), distribution_info varchar2(50), no_of_walls number) ;
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873733','0',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873740','0',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873757','0',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873726','0',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874211','1',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874235','1',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874099','1',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874167','1',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874174','1',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874228','1',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874051','2',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874013','2',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874082','2',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874068','2',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874075','2',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874655','3',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874457','4',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874440','4',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874464','4',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874914','5',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874938','5',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874921','5',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874679','6',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874730','6',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102875225','7',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102875256','7',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102875041','8',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102875034','8',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874990','8',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102875027','8',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102874969','8',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872354','9',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872378','9',1);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872965','0-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872996','0-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873030','0-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872958','0-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872552','1-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872460','1-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872477','1-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872583','1-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872446','1-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872491','1-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872545','1-2',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873061','4-5',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873146','4-5',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873092','4-5',2);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873412','1-6-7',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873429','1-6-7',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873436','1-6-7',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873467','1-6-7',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872866','3-4-5',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872835','3-4-5',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872842','3-4-5',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872804','3-4-5',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102872880','3-4-5',3);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873863','4-7-8-9',4);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873894','4-7-8-9',4);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873917','4-7-8-9',4);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873962','4-7-8-9',4);
insert into test_cpy (unit_no,distribution_info,no_of_walls) values ('00900962950102873795','4-7-8-9',4);
commit;