Hi Greg,
I created a stored procedure that I believe gives you the result you are
looking for.
My sample will take 1 to 5 widget/quantities but can easily be expanded to
handle more widgets per box.
You call the stored procedure as follows.
call get_box_type ('widget1', 3, 'widget2', 1, 'widget3', 1);
call get_box_type ('widget2', 1, 'widget3', 1);
Note I added an order # to the table since there has to be a way to identify
different shipments that used the same box type.
Its also possible for the query to return multiple records with the same or
different box types. So you probably should have a method to return the box
size (with shipping costs) that would result in the lowest cost.
============================================================================
==============
-- CREATE OR REPLACE TABLE BOX_HISTORY
-- FOR SYSTEM NAME BOXHISTORY
-- (order_nbr INT NOT NULL WITH DEFAULT,
-- box_type CHARACTER ( 10) NOT NULL WITH DEFAULT,
-- qty INT NOT NULL WITH DEFAULT,
-- sku CHARACTER ( 10) NOT NULL WITH DEFAULT);
-- DELETE FROM BOX_HISTORY
-- WHERE 1 = 1;
-- INSERT INTO BOX_HISTORY
-- (order_nbr, box_type, qty, sku)
-- VALUES (1, 'boxA', 3, 'widget1'),
-- (1, 'boxA', 1, 'widget2'),
-- (1, 'boxA', 1, 'widget3'),
-- (2, 'boxB', 1, 'widget2'),
-- (2, 'boxB', 1, 'widget3'),
-- (3, 'boxA', 1, 'widget1'),
-- (3, 'boxA', 1, 'widget2'),
-- (3, 'boxA', 1, 'widget4');
-- sample stored procedure calls
-- call get_box_type ('widget1', 3, 'widget2', 1, 'widget3', 1);
-- call get_box_type ('widget2', 1, 'widget3', 1);
CREATE OR REPLACE PROCEDURE get_box_type
(IN item1 CHAR(10),
IN item1_qty INT,
IN item2 CHAR(10) DEFAULT ' ',
IN item2_qty INT DEFAULT 0,
IN item3 CHAR(10) DEFAULT ' ',
IN item3_qty INT DEFAULT 0,
IN item4 CHAR(10) DEFAULT ' ',
IN item4_qty INT DEFAULT 0,
IN item5 CHAR(10) DEFAULT ' ',
IN item5_qty INT DEFAULT 0)
LANGUAGE SQL
PROGRAM NAME GETBOXTYPE
DYNAMIC RESULT SETS 1
BEGIN
DECLARE item_qty INT;
DECLARE C1 CURSOR FOR
with s0 as
(SELECT order_nbr, box_type, count(box_type) as item_type_qty
FROM BOX_HISTORY
GROUP BY order_nbr, box_type
ORDER BY order_nbr, box_type),
s1 as
(SELECT * FROM BOX_HISTORY s1 WHERE s1.sku = item1 and s1.qty =
item1_qty),
s2 as
(SELECT * FROM BOX_HISTORY s2 WHERE s2.sku = item2 and s2.qty =
item2_qty),
s3 as
(SELECT * FROM BOX_HISTORY s3 WHERE s3.sku = item3 and s3.qty =
item3_qty),
s4 as
(SELECT * FROM BOX_HISTORY s4 WHERE s4.sku = item4 and s4.qty =
item4_qty),
s5 as
(SELECT * FROM BOX_HISTORY s5 WHERE s5.sku = item5 and s5.qty =
item5_qty)
select s0.box_type, s1.qty as item1_qty, s1.SKU as item1_SKU,
s2.qty as item2_qty, s2.SKU as item2_SKU,
s3.qty as item3_qty, s3.SKU as item3_SKU,
s4.qty as item4_qty, s4.SKU as item4_SKU,
s5.qty as item5_qty, s5.SKU as item5_SKU
from s0
left join s1 on s0.order_nbr = s1.order_nbr
left join s2 on s0.order_nbr = s2.order_nbr
left join s3 on s0.order_nbr = s3.order_nbr
left join s4 on s0.order_nbr = s4.order_nbr
left join s5 on s0.order_nbr = s5.order_nbr
where s0.item_type_qty = item_qty and
(item1 <> ' ' and s1.box_type is not null or item1 = ' ' and
s1.box_type is null) and
(item2 <> ' ' and s2.box_type is not null or item2 = ' ' and
s2.box_type is null) and
(item3 <> ' ' and s3.box_type is not null or item3 = ' ' and
s3.box_type is null) and
(item4 <> ' ' and s4.box_type is not null or item4 = ' ' and
s4.box_type is null) and
(item5 <> ' ' and s5.box_type is not null or item5 = ' ' and
s5.box_type is null);
-- calculate the number of different items
set item_qty = 1;
if item2 <> ' ' then set item_qty = item_qty + 1; end if;
if item3 <> ' ' then set item_qty = item_qty + 1; end if;
if item4 <> ' ' then set item_qty = item_qty + 1; end if;
if item5 <> ' ' then set item_qty = item_qty + 1; end if;
OPEN C1;
RETURN;
END
============================================================================
================================================
message: 1
date: Fri, 26 Aug 2022 20:57:18 +0000
from: Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
subject: SQL Question
We have a historical table containing what items we previous packed into
different boxes. I'd like to draw on that historical data to extrapolate
what box to use when packing the same items/qty again.
BOX TYPE, QTY, SKU
Data might look like this
boxA, 3, widget1
boxA, 1, widget2
boxA, 1, widget3
boxB, 1, widget2
boxB, 1, widget3
And so forth. This means that "boxA" can hold 3 of widget1 and 1 each of
widget2 and widget3
I would like to query the table to so what box type to use when I have 1 of
widget2 and 1 of widget3 (i.e. find "boxB"). In other words, find a
"matching" box type for my current two rows.
1 widget2
1 widget3
Is this even possible with SQL?
[Logo]<
https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<
http://www.totalbizfulfillment.com>
midrange.com
