• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Using Cross Apply Query with Multiple Records

I am still very new to CTE & CROSS APPLY. I have this code:

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, b.date, bt.RunningTotal
          CASE WHEN bt.RunningTotal <= @qtyNeeded THEN b.qty 
                   ELSE b.qty - (bt.RunningTotal - @qtyNeeded) 
          END AS AdjQty
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


If I have another table called OrderList, which specifies the code and quantity parameters for each order:

DECLARE @OrderList TABLE (
    id int,
    code varchar(5),
    qty int,
)

Where code corresponds to @code and qty corresponds to @qtyNeeded

Is there an easier way to join @OrderList to the query above without using a while loop or cursor?

0
Candidochris
Asked:
Candidochris
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You can just JOIN in that data unless I am missing something - no need for loop or a cursor. Are you getting a specific issue when you INNER or LEFT OUTER JOIN @OrderList to the rest of the query. It would seem you can join on b.code since it equals @code and qty = @qtyNeeded.
0
 
CandidochrisAuthor Commented:
You're right, I had a mistake in my query.
0

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now