pick enough items from several locations without doing rbar

Question

I have a warehouse, where each itemnumber can be in several locations. I have a preferred order where to pick from.

ITEM   LOC   IN_STOCK
item_1 loc_1 3
item_1 loc_2 3
item_1 loc_3 2
item_2 loc_1 4

The preferred order is a function of the location name, for the sake of simplicity, lets just say it is order by loc asc

I have an order

item_1 4
item 2 2

I want a picklist to enter into a third table.

item_1 loc_1 3
item_1 loc_2 1
item_2 loc_1 2

If I just make a join, I get three rows of item_1

I'd like to do it in a single sql-statement, not a RBAR, Row By Agonizing Row loop.

I'm using Mssql 2008

Answer 1

Please try using CTE:

;with T as(
  select *,ROW_NUMBER() over(partition by item order by in_stock, LOC desc) RNum
  From warehouse)
select 
    x.Item,
    x.Loc,
    (case when SM-ItemNum<=in_stock then SM-ItemNum else in_stock end) as in_stock
From(
    select *,(select SUM(b.In_stock) from T b where b.item=a.item and b.RNum<=a.RNum) SM
    from T a
)x inner join orders c on x.item=c.item
where SM-ItemNum>0

Check solution here SQL Fiddle Demo