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