Have a table that contains a list of orders with parts.

I want to be able to select the orders that are most similar first. This is
so that the warehouse staff
can quickly pick the orders as its more effecient to pick orders that are
similar at the same time than
it is to just pick each order on its own(as the warehouse staff would be
having to repick the same item,
when they could have done both orders at one time

Example :

Table Orders:

ORDER SKU
1 ABC
1 DEF
1 123
1 321

2 789
2 ABC

3 DEF
3 123
3 321

4 YUJ
4 JYH
4 SXC

5 ABC
5 789


So looking at the part(SKU) in the order table, order 5 and 2 are exactly
the same, so I would want to pick those first
Then order 1 and 3 are almost the same, as order 1 has one extra item only.
Since order 4 has nothing similar it would be last

So I would want to end up with a select that returned

Order
2
5
1
3
4

This way the warehouse is picking orders in the quickest possible way.

I can't think of a way to do this in SQL .. Does anybody have a suggestion
or solution that might work ???

"Ethan Hunt here from IMF"...

DECLARE @Sample TABLE
(
OrderID INT,
SKU CHAR(3)
)

INSERT @Sample
SELECT 1, 'ABC' UNION ALL
SELECT 1, 'DEF' UNION ALL
SELECT 1, '123' UNION ALL
SELECT 1, '321' UNION ALL
SELECT 2, '789' UNION ALL
SELECT 2, 'ABC' UNION ALL
SELECT 3, 'DEF' UNION ALL
SELECT 3, '123' UNION ALL
SELECT 3, '321' UNION ALL
SELECT 4, 'YUJ' UNION ALL
SELECT 4, 'JYH' UNION ALL
SELECT 4, 'SXC' UNION ALL
SELECT 5, 'ABC' UNION ALL
SELECT 5, '789'

SELECT OrderID
FROM (
SELECT s1.OrderID,
MIN(s1.Items) AS Items,
SUM(CASE WHEN s1.OrderID = s2.OrderID THEN 0 ELSE 1 END) AS Hits,
CASE
WHEN MIN(s1.Items) < MIN(s2.Items) THEN MIN(s2.Items)
ELSE MIN(s1.Items)
END AS Cases
FROM (
SELECT OrderID,
COUNT(*) OVER (PARTITION BY OrderID) AS Items,
SKU
FROM @Sample
) AS s1
INNER JOIN (
SELECT OrderID,
COUNT(*) OVER (PARTITION BY OrderID) AS Items,
SKU
FROM @Sample
) AS s2 ON s2.SKU = s1.SKU
GROUP BY s1.OrderID,
s2.OrderID
) AS d
GROUP BY OrderID
ORDER BY MAX(1.0 * Hits / Cases) DESC

Just in case, throw in an "OrderID" in your ORDER BY case.

ORDER BY MAX(1.0 * Hits / Cases) DESC,
OrderID

"Peso" <peso (AT) developerworkshop (DOT) net> wrote

Hi Ethan!!! My message didn't self destruct....

Thanks for your answer...

Is there away to adapt this query so that it would work, without a problem
if I didn't know the part/SKU ids.. The problem is that in a production
environment, there would be tens of thousands of parts in the warehouse, so
I could not do this for every possible parts.

Yes. The first part is only for mimicing your environment and have some
sample data to work with.
This is the query you want.


SELECT OrderID
FROM (
SELECT s1.OrderID,
MIN(s1.Items) AS Items,
SUM(CASE WHEN s1.OrderID = s2.OrderID THEN 0 ELSE 1 END) AS Hits,
CASE
WHEN MIN(s1.Items) < MIN(s2.Items) THEN MIN(s2.Items)
ELSE MIN(s1.Items)
END AS Cases
FROM (
SELECT OrderID,
COUNT(*) OVER (PARTITION BY OrderID) AS Items,
SKU
FROM {YourTableNameHere}
) AS s1
INNER JOIN (
SELECT OrderID,
COUNT(*) OVER (PARTITION BY OrderID) AS Items,
SKU
FROM {YourTableNameHere}
) AS s2 ON s2.SKU = s1.SKU
GROUP BY s1.OrderID,
s2.OrderID
) AS d
GROUP BY OrderID
ORDER BY MAX(1.0 * Hits / Cases) DESC,
OrderID

You mght find this article useful "Data Mining on a Budget" :

http://www.tdan.com/view-perspectives/5343

In this case, the BBQ company had ~5000 confgurations that had ever
been ordered, so it was easy to put them itno a lookup table for the
box size on the packing slip. Why play 3D Teteris every time you get
an order? In your case, yoiu could classify configurations in some way
("orders that need a forklift", "orders that need refrigeration", or
whatever makes sense) then sort on that classification.