3

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

Leif Neland
  • 1,416
  • 1
  • 17
  • 40
  • Where you are specifying the preferred order of picking items? – TechDo Dec 20 '13 at 11:30
  • Isnt this something you would like to do with a Stored Procedure ?? I mean check the stock 1st and then add it to the third table. – M.Ali Dec 20 '13 at 11:37
  • M.Ali Like first taking 4 item_1 from loc_1, then the trigger sees that there are -1 in stock, adding 1, and search for the remaining 1 in another location? RBAR seems more manageable :-) – Leif Neland Dec 20 '13 at 11:41

1 Answers1

0

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

TechDo
  • 18,398
  • 3
  • 51
  • 64
  • @PeterRing Please mention the reason. – TechDo Dec 21 '13 at 04:12
  • Excusse me i was a little tired yesterday. Try it with: create table Orders(ITEM nvarchar(100), ItemNum int); insert into Orders values ('item_1',7), ('item_2',2); And you will see the problem. – PeterRing Dec 21 '13 at 20:06
  • I also found it non-working. http://sqlfiddle.com/#!3/43ce7/1 Reduced ('item_1','loc_1',1), and now only get 2 item_1 even when 4 is ordered. Not sure how it is supposed to work, and why it isn't – Leif Neland Dec 23 '13 at 08:44