SQL Query Help for Queue Quantities

Question

I have a database which has an orders table and inventory table.

The order-items table has a 1 record per 1 qty layout, so if a person places an order for 7 'ABC's, and 4 'XYZ's, I get 11 records in the table.

id, item, qtyNum
01 ABC 1 
02 ABC 2 
03 ABC 3 
04 ABC 4 
05 ABC 5
06 ABC 6
07 ABC 7
08 XYZ 1
09 XYZ 2
10 XYZ 3
11 XYZ 4

The inventory table has a quantity/item per-location layout, so I can have 20 of something in stock, but it can be (in the worst case) in 20 separate locations. So for our example, I might have the following inventory:

Qty, Item, Loc, Date
3 'ABC' in Location L1 with date 1990
2 'ABC' in Location L2 with date 1992
5 'ABC' in Location L3 with date 2003
4 'ABC' in Location LH with date 2004
1 'XYZ' in Location L4 with date 1990
2 'XYZ' in Location L5 with date 1993
9 'XYZ' in Location L6 with date 2001
2 'XYZ' in Location LJ with date 2004

*The H and J have no special significance! Just driving the point home that they are the newest

The result set should pull as many as possible from the oldest locations first, so for this example I end up with the following 'pick queue':

Pick 3 'ABC' from L1
Pick 2 'ABC' from L2
Pick 2 'ABC' from L3
Pick 1 'XYZ' from L4
Pick 2 'XYZ' from L5
Pick 1 'XYZ' from L6

I do have a solution which involves a lot of views which are joined to multiple times with outer joins and crazy stuff like that and I'm just curious if there is a simple/elegant solution for this problem? I could do it in code no problem, but in SQL I'm no guru.
MSSQL 2008

Answer 1

Whew, this was a tough one for me; I'm sure there are more elegant solutions than this, but this is what I came up with:

--test data
DECLARE @orders TABLE
    (
      ID INT IDENTITY(1, 1) ,
      item CHAR(3) ,
      Qty INT
    )
INSERT  INTO @orders
        ( item, Qty )
VALUES  ( 'abc', 1 ),
        ( 'abc', 2 ),
        ( 'abc', 3 ),
        ( 'abc', 4 ),
        ( 'abc', 5 ),
        ( 'abc', 6 ),
        ( 'abc', 7 ),
        ( 'xyz', 1 ),
        ( 'xyz', 2 ),
        ( 'xyz', 3 ),
        ( 'xyz', 4 )

DECLARE @ItemLoc TABLE
    (
      Qty INT ,
      ITEM CHAR(3) ,
      Loc CHAR(2) ,
      Dt INT
    )
INSERT  INTO @ItemLoc
        ( Qty, ITEM, Loc, Dt )
VALUES  ( 3, 'abc', 'L1', 1990 ),
        ( 2, 'abc', 'L2', 1992 ),
        ( 5, 'abc', 'L3', 2003 ),
        ( 4, 'abc', 'LH', 2004 ),
        ( 1, 'xyz', 'L4', 1990 ),
        ( 2, 'xyz', 'L5', 1993 ),
        ( 9, 'xyz', 'L6', 2001 ),
        ( 2, 'xyz', 'LJ', 2004 ) ;


/*looks complicated, and it is
    I use a cte to try to ease it up a bit,
    but I first identify a running sum of items 
    in the bins, and a pull order based on item 
    and year.
*/  


WITH    cte
          AS ( SELECT   a.Qty ,
                        a.Item ,
                        a.Loc ,
                        a.Dt ,
                        a.RunningSum ,
                        a.PullOrder ,
                        b.Qty AS OrderQty
               FROM     ( SELECT    Qty ,
                                    Item ,
                                    Loc ,
                                    Dt ,
                                    RunningSum = ( SELECT   SUM(Qty)
                                                   FROM     @ItemLoc il1
                                                   WHERE    il1.Item = il.Item
                                                            AND il1.Dt <= il.Dt
                                                 ) ,
                                    PullOrder = ROW_NUMBER() OVER ( PARTITION BY Item ORDER BY Dt )
                          FROM      @ItemLoc il
                        ) a
                        JOIN ( SELECT   item ,
                                        MAX(qty) AS qty
                               FROM     @orders o
                               GROUP BY item
                             ) b ON a.Item = b.item
             )
    /* I then use the cte to a) identify the minimum bin
       which has a RunningSum of items greater than the OrderQty,
       and b) pick all of the items in the bins below that, and 
       c) pick the remaining items from the last bin
   */         


    SELECT  Pick = CASE WHEN RunningSum <= OrderQty THEN Qty
                        ELSE OrderQty - ( SELECT    SUM(Qty)
                                          FROM      cte c3
                                          WHERE     c3.item = c1.ITem
                                                    AND c3.RunningSum < c1.RunningSum
                                        )
                   END ,
            c1.Item ,
            Loc
    FROM    cte c1
            JOIN ( SELECT   Item ,
                            MIN(PullOrder) AS po
                   FROM     cte c2
                   WHERE    RunningSum >= OrderQty
                   GROUP BY Item
                 ) x ON c1.Item = x.Item
                        AND c1.PullOrder <= x.po

Answer 2

After revisiting this problem, I decided that it would be far more efficient to create a table-valued function and

The query as it stands right now went from 1:45 to 0:03. Awesome.

Unfortunately I cant post the code, but the general pseudo code for the solution is:

Create table variable to contain all the available pick locations which can be in any way tied to an open order.

Create a second table variable to contain all the open orders. Include whatever columns you need for statuses of the individual items on each order.

Create the results table (or do this first, if you're using a table-valued function) which contains the necessary information for your pick process. (So order#, item #, and what location# you want it to pull from.)

Iterate:

from the number of records in open orders to 1 of the open orders table, joining where the location has qty > 0. Store each pass into the results table.

Reduce the quantity of the location you just inserted into the results table by 1, if you had a location. (sometimes an order might not be pickable because of quantity or orderstatus issues, but you still want them in the results for reporting or allocation purposes.) :End Iterate

I appreciate the help Stuart Ainsworth, I just wanted to avoid sub queries and stuff. I managed to write this without doing any joins to the same tables more than once and no sub queries. Bumped yours because its freakin awesome!