Set-based solution possible?
-
I have a situation that I've solved using a loop after I spent half the day trying to come up with a set-based solution. I guess I'm too stubborn.
Here's the scenario: In a warehouse, items are stored in bins and an item can be in multiple bins. When items are received, they are put away into the bins based on a priority. The bins have a maximum quantity as well.
Here's a sample data setup (much simplified from the real database)...
Create Table ItemBin (
Item varchar(80),
BinId varchar(10),
PutawayPriority int,
QtyMax numeric(19,2),
QtyOnHand numeric(19,2)
)
GO
Set NoCount On
Insert Into ItemBin Values ('A1', 'BIN1', 1, 100, 50)
Insert Into ItemBin Values ('A1', 'BIN2', 2, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN3', 3, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN4', 4, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN5', 5, 100, 0)
GO
Declare @QtyToPutAway numeric(19,2)
Set @QtyToPutAway = 275
-- Can I replace loop here?
GO
Drop Table ItemBin
GO
When done... Bins 1-3 should have 100 on hand. Bin 4 should have 25 on hand. Bin 5 will have 0 on hand (no change).
Can this be done with a single Update statement?
-
Not one update statement, hope it helps!
Create Table ItemBin (
Item varchar(80),
BinId varchar(10),
PutawayPriority int,
QtyMax numeric(19,2),
QtyOnHand numeric(19,2))
GO
Set NoCount On
Insert Into ItemBin Values ('A1', 'BIN1', 1, 100, 50)
Insert Into ItemBin Values ('A1', 'BIN2', 2, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN3', 3, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN4', 4, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN5', 5, 100, 0)
GO
Declare @QtyToPutAway numeric(19,2),
@CurrentBin varchar(10),
@Item varchar(80),
@QtyMax Numeric(19,2),
@QtyOnHand Numeric(19,2)
Select 'Test before',* from ItemBin order by binId
Set @QtyToPutAway = 275
Set @Item='A1'
Select @CurrentBin=Min(BinId)
From ItemBin
Where Item=@Item And
QtyOnHand<QtyMax
Select @QtyMax=QtyMax,
@QtyOnHand=QtyOnHand
From ItemBin
Where BinId=@CurrentBin And
Item=@Item
While Not (@CurrentBin Is Null) And @QtyToPutAway>0
Begin
If @QtyMax-@QtyOnHand>=@QtyToPutAway
Begin
Update ItemBin
Set QtyOnHand=QtyOnHand+@QtyToPutAway
Where Item=@Item ANd
BinId=@CurrentBin
Set @QtyToPutAway=0
End
Else
Begin
Update ItemBin
Set QtyOnHand=@QtyMax
Where Item=@Item And
BinId=@CurrentBin
Set @QtyToPutAway=@QtyToPutAway-(@QtyMax-@QtyOnHand)
End
If @QtyToPutAway>0
Begin
Select @CurrentBin=Min(BinId)
From ItemBin
Where Item=@Item And
QtyOnHand<QtyMax And
BinId>@CurrentBin
Select @QtyMax=QtyMax,
@QtyOnHand=QtyOnHand
From ItemBin
Where BinId=@CurrentBin And
Item=@Item
End
End
If @QtyToPutAway>0
Select 'Not enough space in bins',@QtyToPutAway
Select 'Test after',* from ItemBin order by binId
GO
Drop Table ItemBin
GO
-
You can try this. It doesn't do stuff like checking that you have enough overall capacity in your bins (you'll need another a separate error-checking query to do that). But it is set based.
declare @QtyToPutAway numeric(19,2)
set @QtyToPutAway = 275
declare @Item varchar(80)
set @Item = 'A1'
update ItemBin
set QtyOnHand = QtyOnHand + QtyActualForThisBin
from
ItemBin [AllBins],
(select
*
,QtyActualForThisBin = CASE WHEN QtyMaxForThisBin <= QtyStillToPutAway THEN QtyMaxForThisBin ELSE QtyStillToPutAway END
from
(select
Item,
BinId,
QtyStillToPutAway = isnull((select @QtyToPutAway - sum(QtyMax - QtyOnHand)
from ItemBin b
where b.Item = @Item and b.PutawayPriority < a.PutawayPriority),@QtyToPutAway),
QtyMaxForThisBin = (QtyMax - QtyOnHand)
from ItemBin a
) [BinCapacities]
) [BinAllocations]
where
[AllBins].Item = [BinAllocations].Item
and [AllBins].BinId=[BinAllocations].BinId
and [BinAllocations].QtyActualForThisBin > 0
-- Yuk! so much for nicely formatting the query before pasting it in here.
Cheers,
- Mark -
Some of the posters manage to keep the formatting, how do they post there text??
-
Use the code tags... You can insert them using the hash key button on the 'Format' bar over the message box.
-
If you don't mind having an intermediate help table (temporary or otherwise) you could use something like this - FillList may even prove usefull.
/*
Create Table ItemBin (
Item varchar(80),
BinId varchar(10),
PutawayPriority int,
QtyMax numeric(19,2),
QtyOnHand numeric(19,2)
)
Set NoCount On
Insert Into ItemBin Values ('A1', 'BIN1', 1, 100, 50)
Insert Into ItemBin Values ('A1', 'BIN2', 2, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN3', 3, 200, 0)
Insert Into ItemBin Values ('A1', 'BIN4', 4, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN5', 5, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN6', 6, 150, 0)
Insert Into ItemBin Values ('A1', 'BIN7', 7, 250, 0)
Insert Into ItemBin Values ('A2', 'BIN1', 1, 100, 50)
Insert Into ItemBin Values ('A2', 'BIN2', 2, 100, 0)
Insert Into ItemBin Values ('A2', 'BIN3', 3, 200, 0)
Insert Into ItemBin Values ('A3', 'BIN4', 1, 100, 0)
Insert Into ItemBin Values ('A3', 'BIN5', 2, 100, 0)
Insert Into ItemBin Values ('A3', 'BIN6', 3, 150, 0)
Insert Into ItemBin Values ('A3', 'BIN7', 4, 250, 0)
-- Drop Table ItemBin
*/
Declare
@QtyToPutAway numeric(19,2),
@ThisItem varchar(80)
Select
@QtyToPutAway = 276,
@ThisItem = 'A1'
-- drop table #FillList
select b.Item,b.BinId,b.QtyMax,min(b.QtyOnHand)QtyOnHand,
-- fill up these bins
sign(1-sign(sum(a.QtyMax-a.QtyOnHand)- @QtyToPutAway))*(b.QtyMax-b.QtyOnHand)+
-- and put the rest in here
(b.QtyMax-(sum(a.QtyMax-a.QtyOnHand)- @QtyToPutAway))*
(sign(1+sign(b.QtyMax-(sum(a.QtyMax-a.QtyOnHand)- @QtyToPutAway)))-
sign(1-sign(sum(a.QtyMax-a.QtyOnHand)- @QtyToPutAway)))QtyToFill
--into #FillList
from ItemBin a, ItemBin b
where
b.Item = @ThisItem and
a.Item = b.Item and
b.PutawayPriority+1 > a.PutawayPriority
group by b.Item, b.PutawayPriority,b.BinId,b.QtyMax,b.QtyOnHand
--select * from #FillList
/*
update ItemBin
set QtyOnHand = a.QtyOnHand+QtyToFill
from ItemBin a, #FillList b
where a.Item = b.Item
and a.BinId = b.BinId
*/
regards clive
-
quote:
Not one update statement, hope it helps!While Not (@CurrentBin Is Null) And @QtyToPutAway>0
Thanks for your suggestion. But you are using a loop, which is what I was hoping to avoid. I have a loop that works. It looks like this:
Create Table ItemBin (
Item varchar(80),
BinId varchar(10),
PutawayPriority int,
QtyMax numeric(19,2),
QtyOnHand numeric(19,2)
)
GO
Set NoCount On
Insert Into ItemBin Values ('A1', 'BIN1', 1, 100, 50)
Insert Into ItemBin Values ('A1', 'BIN2', 2, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN3', 3, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN4', 4, 100, 0)
Insert Into ItemBin Values ('A1', 'BIN5', 5, 100, 0)
GO
Declare
@QtyToPutAway numeric(19,2),
@QtyAvailInBin numeric(19,2),
@BinId varchar(10),
@Item varchar(80),
@Priority int
Set @QtyToPutAway = 275
Set @Item = 'A1'
Set @Priority = 0
Print 'Bins before allocation'
Select * From ItemBin
While Exists (
Select * From ItemBin
Where Item = @Item And PutawayPriority > @Priority
) And @QtyToPutAway > 0
Begin
Select Top 1
@BinId = BinId,
@Priority = PutawayPriority,
@QtyAvailInBin = QtyMax - QtyOnHand
From ItemBin
Where Item = @Item And PutawayPriority > @Priority
Order By PutawayPriority
If @QtyToPutAway < @QtyAvailInBin
Begin
Set @QtyAvailInBin = @QtyToPutAway
End
Update ItemBin
Set QtyOnHand = QtyOnHand + @QtyAvailInBin
Where Item = @Item And BinId = @BinId
Set @QtyToPutAway = @QtyToPutAway - @QtyAvailInBin
End
Print 'Bins after allocation'
Select * From ItemBin
GO
Drop Table ItemBin
GOI was hoping a set-based solution would be more elegant and efficient.
-
quote:
If you don't mind having an intermediate help table (temporary or otherwise) you could use something like this - FillList may even prove usefull.Thanks for the suggestion. I didn't state this, but I was trying to avoid a temporary table as well. I had come up with a solution that used a temp table. I was also hoping to avoid using a correlated subquery, for which I had also figured out a solution.
I'd like to thank everybody for their suggestions. It made me aware of some interesting techniques. cql's suggestion uses a combination of the SGN and SUM functions that might be useful in the future. And mccork's use of multiple derived tables was something I hadn't thought of.
-
Try this, I hope it will help:
/*
Assumption 1:
PutawayPriority is unique for each Item (no two bins for an item can have the same priority)
If this is not true, the some another criteria should be used to UNIQUELLY order bins,
like concatenating the BinId to the priority (converted to varchar).
Assumption 2:
The procedure does not handle the case when the quantity to put away
is bigger than the available space in all bins. It should be checked at the beginning,
and maybe the @QtyToPutAway should be OUTPUT parameter, returning the remaining quantity that
is not possible to put away.
The basic idea is:
1. Find all bins that should be updated at all (this is the WHERE part of the UPDATE statement).
You can put away items in bins until all items are used. Due to the priority,
the minimum priority is found, so all bins with that priority OR HIGHER will consume all
items that should be put away.
2. Update all quantities on hand in all bins having the priority NOT LOWER than the one found
in step 1 as follows:
- if the quantity that is left after filling all bins with higher priority is bigger than
the available space, than the bin is filled up to its maximum quantity.
- otherwise (only for the last bin, and only if any items are left until filling
all other bins ut to their maximum) increase the quantity with the rest of the items.
Some useful indexes may be created on (ItemId, Priority) or (ItemId, Proirity, BinId)
if the table is large, due to the compexity of the SQL.
Also, appropriate locking may be added (like SELECT-ing all records for the item WITH (UPDLOCK).
*/
create procedure usp_PutawayQuantity (@Item varchar(80), @QtyToPutAway numeric(19,2))
as
set nocount on
update
ItemBin
set
QtyOnHand =
case
when QtyMax - QtyOnHand < @QtyToPutAway -
(select isnull(sum(QtyMax - QtyOnHand), 0)
from ItemBin IB2
where (Item = @Item) and (IB2.PutawayPriority < ItemBin.PutawayPriority))
then QtyMax
else
QtyOnHand + @QtyToPutAway -
(select isnull(sum(QtyMax - QtyOnHand), 0)
from ItemBin IB2
where (Item = @Item) and (IB2.PutawayPriority < ItemBin.PutawayPriority))
end
where
(Item = @Item) and
PutawayPriority <=
(select
min(PutawayPriority)
from
ItemBin IB1
where
@QtyToPutAway <=
(select sum(QtyMax - QtyOnHand)
from ItemBin IB2
where (Item = @Item) and (IB2.PutawayPriority <= IB1.PutawayPriority)
)
)
goEdited by - g_smilevski on 06/06/2003 05:55:55 AM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply