How can I find the right size box for each product?

Question

I am sorry for re-uploading this quesiton, but I really want the answer.

Kindly allow me to ask this question again and hope your kind support.

---

The question is to find the right size box which allow the logistic business to save the money when shipping.

We have 2 tables which are boxes and products.

Boxes table contains each ID and dimensions per box. 'w' for wide, 'd' for depth and 'h' for height. Please assume we have just 3 box samples for our convenience.

Products table includes also product ID, dimensions. Dimensions has the same meaning as boxes table. 'layable' means the product can be packaged not only straight position but also layable position. For instance product 'g' is a fragile bottle not being able to put horizontal position in the box. Thus this is 'n' in layable column.

This question needs to query each product ID with the right size box. The right size box means the product needs to be shipped with box that is the least space.

Hope your kind help. Thanks.

boxes:

BOX_SIZE	W	D	H
S	353	250	25
M	450	350	160
L	610	460	460

products:

ID	W	D	H	LAYABLE
a	350	250	25	y
b	450	250	160	y
c	510	450	450	y
d	350	250	25	y
e	550	350	160	y
f	410	400	430	n
g	350	240	25	n
h	450	350	160	n
i	310	360	430	n
j	500	500	600	y

Expected output:

ID	BOX_SIZE
a	S
b	M
...	....
...	....
...	....
g	S
h	M
i	L
j	not available

Statements to create and populate the tables for testing:

create table boxes
    ( box_size char(1) primary key
    , w        number  not null
    , d        number  not null
    , h        number  not null
    )
;

insert into boxes (box_size, w, d, h) values ('S', 353, 250,  25);
insert into boxes (box_size, w, d, h) values ('M', 450, 350, 160);
insert into boxes (box_size, w, d, h) values ('L', 610, 460, 460);

create table products
    ( id      varchar2(10) primary key
    , w       number       not null
    , d       number       not null
    , h       number       not null
    , layable char(1)      check(layable in ('y', 'n'))
    )
;

insert into products (id, w, d, h, layable) values ('a', 350, 250,  25, 'y');
insert into products (id, w, d, h, layable) values ('b', 450, 250, 160, 'y');
insert into products (id, w, d, h, layable) values ('c', 510, 450, 450, 'y');
insert into products (id, w, d, h, layable) values ('d', 350, 250,  25, 'y');
insert into products (id, w, d, h, layable) values ('e', 550, 350, 160, 'y');
insert into products (id, w, d, h, layable) values ('f', 410, 400, 430, 'n');
insert into products (id, w, d, h, layable) values ('g', 350, 240,  25, 'n');
insert into products (id, w, d, h, layable) values ('h', 450, 350, 160, 'n');
insert into products (id, w, d, h, layable) values ('i', 310, 360, 430, 'n');
insert into products (id, w, d, h, layable) values ('j', 500, 500, 600, 'y');    

commit;

Answer 1

The key, of course, is the join between the two tables. I show it separately first, rather than the complete query, to help understanding. For each item, we find ALL the box sizes that can accommodate the item.

In all cases, the match is possible if product height <= box height, and the other two dimensions fit, in either permutation (products can always be rotated to fit in the box, whether they are layable or not).

Only for layable products, we are allowed to rotate the product in all three dimensions to fit them in boxes. This means that, for layable products only, we can compare product width or depth to box height, and compare the two remaining dimensions of the product to box width and depth.

Once we understand what I just said (as the way we would do this without computers, just with pencil on paper), the translation into code is almost automatic:

select p.id, b.box_size
from   products p left outer join boxes b
       on
            p.h <= b.h and least   (p.w, p.d) <= least   (b.w, b.d)
                       and greatest(p.w, p.d) <= greatest(b.w, b.d)
       or
       p.layable = 'y'
          and
          ( p.w <= b.h and least   (p.h, p.d) <= least   (b.w, b.d)
                       and greatest(p.h, p.d) <= greatest(b.w, b.d)
            or
            p.d <= b.h and least   (p.w, p.h) <= least   (b.w, b.d)
                       and greatest(p.w, p.h) <= greatest(b.w, b.d)
          )
;

Output:

ID  BOX_SIZE
--- --------
a   S       
a   M       
a   L       
b   M       
b   L       
c   L       
d   S       
d   M       
d   L       
e   L       
f   L       
g   S       
g   M       
g   L       
h   M       
h   L       
i   L       
j      

For each product, we found ALL the sizes that would work.

Notice the outer join in the query, to include products that don't fit in ANY box size; that is the case of product j, which appears at the end of the output. Note that I use null as a marker for "not available" - the words "not available" add no valuable information over the simple use of null.

The next step is a simple aggregation - for each product, find the smallest size that works. The best tool for this is the FIRST aggregate function (as used below). We must order by box size; since the sizes are S, M, L (which are in reverse alphabetical order just by accident), I use the decode() function to assign 1 to S, 2 to M, 3 to L. The aggregate query finds the "first" size that works for each product.

The important thing here is that the query can be generalized easily to any number of possible "box sizes" - even when not all three dimensions are in increasing order. (You could also have boxes with only one of the dimensions very large while the others are small, etc.). You can order by box volume, or you can store in the boxes table an order of preference, equivalent to what I do in the query with the decode() function.

In the end, the query and output look like this. Note that I used nvl() in the select clause to generate 'not available' for the last item, in case you really need it (which I doubt, but it's not my business problem.)

select p.id, 
       nvl(  min(b.box_size) keep (dense_rank first 
             order by decode(b.box_size, 'S', 1, 'M', 2, 'L', 3))
          , 'not available') as box_size
from   products p left outer join boxes b
       on
            p.h <= b.h and least   (p.w, p.d) <= least   (b.w, b.d)
                       and greatest(p.w, p.d) <= greatest(b.w, b.d)
       or
       p.layable = 'y'
          and
          ( p.w <= b.h and least   (p.h, p.d) <= least   (b.w, b.d)
                       and greatest(p.h, p.d) <= greatest(b.w, b.d)
            or
            p.d <= b.h and least   (p.w, p.h) <= least   (b.w, b.d)
                       and greatest(p.w, p.h) <= greatest(b.w, b.d)
          )
group  by p.id
;

ID  BOX_SIZE
--- --------
a   S       
b   M       
c   L       
d   S       
e   L       
f   L       
g   S       
h   M       
i   L       
j   not available   

Answer 2

So far I can come up with my own answer as below, but this seems to get some triple rows per each product ID. Thus this needs to filter to the smallest box per query. I tired to use min(function) but this leads error.

select p.id, p.h, p.w, p.d, p.layable, case
when p.layable = 'n' then case 
 when p.h <= b.h and 
((greatest(p.w, p.d ) <= greatest(b.w, b.d)) 
and (least(p.w, p.d) <= least(b.w, b.d))) then b.id
else 'no' end
when p.layable = 'y' then case 
when (p.h + p.w + p.d) <= (b.h + b.w + b.d) and 
((greatest(p.h, p.w, p.d) <= greatest(b.h, b.w, b.d))
and (least(p.h, p.w, p.d) <= least(b.h, b.w, b.d))) then b.id
else 'no' end
 else 'not available' end
from products p, boxes b
order by p.id;