sql server query inner join ordered by date based on quantity

Posted on 2011-10-07
Medium Priority
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
  • 2
LVL 52

Accepted Solution

_agx_ earned 2000 total points
ID: 36934618
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.

    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, b.date, bt.RunningTotal
            ( 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 52

Assisted Solution

_agx_ earned 2000 total points
ID: 36934651
> 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, b.date, bt.RunningTotal,
           CASE WHEN bt.RunningTotal <= @qtyNeeded THEN b.qty
                   ELSE b.qty - (bt.RunningTotal - @qtyNeeded)
          END AS AdjQty

Author Closing Comment

ID: 36942181
Awesome, that is exactly what I needed.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 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