This is a really challenge: We got a vertical warehouse where Items can be stored on 20 different shelfs.
Work preparation gathers all items that are necessary to start the production order. Switching shelfs takes quite a lot of time, so in order to optimize the picking process, I want to fill the shelfs in the most intelligent way.
My idea to solve this is the following:
-
In this Example each WorkOrder (WO) requires up to 5 different Items.
-
There are 10 items in the Items Data Base
-
There can be placed 4 items per shelf
-
Based on the data of 10 WO, I create a pivot Table that reflects all occurrences like this:
ITEM ITEM01 ITEM02 ITEM03 ITEM04 ITEM05 ITEM06 ITEM07 ITEM08 ITEM09 ITEM10 --------------------------------------------------------------------------------------------------------- ITEM01 4 0 1 0 0 3 2 0 1 0 ITEM02 0 3 0 1 0 3 0 2 0 0 -
With this I start from the highest occurence and place both related items on shelf1
-
I look for the second highest occurrence and place the related items on shelf2
-
In case that the next lower occurence is in the same row as the highest, then I add the item to shelf1
-
I continue with this filling method until each shelf has some items.
-
Afterwards I continue with those shelfs that have most capacity, but giving preference to those shelfs that already have a match in that same row.
Anybody has some thoughts on that?
The real situation is based on 200'000 WO's and 7'500 different items.
Here is the code to create a parameterized test environment.
declare @totItems int = 10 /* Amount Items
declare @totWO int = 10 /* Amount WO
declare @maxItemsXWO int = 5 /* maximum amount of Items per WO
declare @i int = 1
declare @m int = 1
declare @ItemsWO int
declare @Item int
declare @tempdbITEMS nvarchar(20) = 'tempdb..#Items'
declare @tempdbWO nvarchar(20) = 'tempdb..#WO'
declare @Item_text nvarchar(4) = 'Item'
declare @WO_text nvarchar(2) = 'WO'
IF OBJECT_ID('tempdb..#Items') IS NOT NULL
DROP TABLE #Items
CREATE TABLE #Items (ITEMID NVARCHAR(6))
WHILE @i <= @totItems
BEGIN
INSERT INTO #Items (ITEMID) Values ('Item' + Right(CAST(@i + 100 as nvarchar),2))
set @i = @i+1
END
declare @ItemList nvarchar(max) = ''
select @ItemList += QUOTENAME(ITEMID) + ',' from #Items
set @ItemList = left(@Itemlist, len(@ItemList) - 1)
declare @sql nvarchar(max)
set @sql = 'declare @totItems int = ' + CAST(@totItems as nvarchar) + '
declare @totWO int = ' + CAST(@totWO as nvarchar) + '
declare @maxItemsXWO int = ' + CAST(@maxItemsXWO as nvarchar) + '
declare @i int = 1
declare @m int = 1
declare @ItemsWO int
declare @Item int
IF OBJECT_ID(''' + @tempdbITEMS + ''') IS NOT NULL
DROP TABLE #Items
CREATE TABLE #Items (ITEMID NVARCHAR(6))
WHILE @i <= @totItems
BEGIN
INSERT INTO #Items (ITEMID) Values (''' + @Item_text+ ''' + Right(CAST(@i + 100 as nvarchar),2))
set @i = @i+1
END
IF OBJECT_ID(''' + @tempdbWO + ''') IS NOT NULL
DROP TABLE #WO
CREATE TABLE #WO (WO NVARCHAR(4), ITEMID NVARCHAR(6))
set @i = 1
set @ItemsWO = FLOOR(RAND()*(@maxItemsXWO)+1)
WHILE @i <= @totWO
BEGIN
WHILE @m <= @ItemsWO
BEGIN
set @Item = FLOOR(RAND()*(@totItems)+1)
IF NOT EXISTS (Select WO from #WO where WO = ''' + @WO_text+ ''' + Right(CAST(@i + 100 as nvarchar),2) and ITEMID = ''' + @Item_text+ ''' + Right(CAST(@Item + 100 as nvarchar),2))
BEGIN
INSERT INTO #WO (WO, ITEMID) Values (''' + @WO_text+ ''' + Right(CAST(@i + 100 as nvarchar),2), ''' + @Item_text+ ''' + Right(CAST(@Item + 100 as nvarchar),2))
set @m = @m+1
END
END
set @i = @i+1
set @ItemsWO = FLOOR(RAND()*(@maxItemsXWO)+1)
set @m = 1
END
SELECT * from #WO
SELECT * from (select a.ITEMID, c.ITEMID as ITEM_Occ from #Items a left outer join #WO b on a.ITEMID = b.ITEMID left outer join #WO c on b.WO = c.WO)a
PIVOT ( COUNT(ITEM_Occ) For ITEM_Occ in (' + @ItemList + ')) as pvt'
exec sp_executesql @sql