Link to home
Start Free TrialLog in
Avatar of Candidochris
CandidochrisFlag for United States of America

asked on

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?

ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Candidochris

ASKER

You're right, I had a mistake in my query.