Candidochris
asked on
Using Cross Apply Query with Multiple Records
I am still very new to CTE & CROSS APPLY. I have this code:
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER