sql server query inner join ordered by date based on quantity

Posted on 2011-10-07
Last Modified: 2012-05-12
I have been racking my brain trying to figure out a solution to a problem.

I have a table with items ITEM in it that I am trying to match to bin locations BIN1.
On ITEM, the column ITEM.Code is unique, however an item can be in multiple bins so on BIN1, there can be inifinite bins for a single item.

the ITEM table looks like:

Code | Description | Price
 A      | Widget        | 5.00

So say for item A, I have 20 of them in various bins, so the table BIN1 looks like this, Qty being the # of units in that bin.

Code | Qty | Bin # | Date
A       | 1    | Bin2  | 10/5/2005
A       | 3    | Bin3  | 10/8/2005
A       | 5    | Bin6   | 11/1/2006
A       | 7    | Bin9   | 11/5/2006
A       | 1    | Bin12 | 11/21/2009
A       | 3    | Bin22 | 12/25/2010

Here's the tricky part:

Say I have a transaction that calls for a quantity 8 of Item A and I want to pull items from BIN1 in ascending date order by Bin #, what would you suggest? I was trying to avoid a loop, but I don't think it's possible.

The result set I would be looking for is:

A      | 1    | Bin2  | 10/5/2005
A      | 3    | Bin3  | 10/8/2005
A      | 4   | Bin6   | 11/1/2006
to total 8. Hopefully someone can point me in the right direction. I'm using SQL Server 2008.


Question by:Candidochris
    LVL 51

    Accepted Solution

    Sounds like you need a running total. This thread has some interesting ideas. But I'd recommend running a few profiles, as performance may vary depending on data and usage.

    DECLARE @bin TABLE (
        code varchar(5),
        qty int,
        bin varchar(10),
        date datetime 
    INSERT INTO @bin (code, qty, bin, date)
    SELECT 'A','1','Bin2','10/5/2005' UNION ALL
    SELECT 'A','3','Bin3','10/8/2005' UNION ALL
    SELECT 'A','5','Bin6 ','11/1/2006' UNION ALL
    SELECT 'A','7','Bin9 ','11/5/2006' UNION ALL
    SELECT 'A','1','Bin12','11/21/2009' UNION ALL
    SELECT 'A','3','Bin22','12/25/2010' UNION ALL
    SELECT 'B','22','Bin3','10/8/2005' UNION ALL
    SELECT 'B','16','Bin19','11/1/2006' 
    DECLARE @code varchar(10)
    DECLARE @qtyNeeded int
    SET @code = 'A'
    SET @qtyNeeded = 8
    ;WITH BinsByDate AS
        SELECT code, qty, bin, date, ROW_NUMBER() OVER (ORDER BY date, bin, code) AS RowNum
        FROM   @Bin
        WHERE  code = @code
    SELECT b.code, b.qty, b.bin,, bt.RunningTotal
    FROM   BinsByDate b CROSS APPLY 
                ( SELECT SUM(t.qty) as RunningTotal
                  FROM   BinsByDate t
                  WHERE  t.RowNum <= b.RowNum
                ) bt
    WHERE  bt.RunningTotal - b.Qty <= @qtyNeeded

    Open in new window

    LVL 51

    Assisted Solution

    > The result set I would be looking for is

    To adjust the "qty" to reflect what's used rather than what's available in the bin just add a CASE

    SELECT b.code, b.qty, b.bin,, bt.RunningTotal,
               CASE WHEN bt.RunningTotal <= @qtyNeeded THEN b.qty
                       ELSE b.qty - (bt.RunningTotal - @qtyNeeded)
              END AS AdjQty

    Author Closing Comment

    Awesome, that is exactly what I needed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now