问SQL查询，用于检索最后一条记录，直到采购数量达到库存总量

EN

/* SQL SCRIPT BEGIN */


create table #tmp (PurchaseNo int, ItemCode int, Qty int)


insert into #tmp (PurchaseNo, ItemCode, Qty)


select 

p1.PurchaseNo, p1.ItemCode, sum(t.Qty) as Qty

from

Purchases p1

join 

(

select 
p2.PurchaseNo, 
p2.ItemCode, p2.Qty 
from
Purchases p2

) t on p1.PurchaseNo <= t.PurchaseNo and p1.ItemCode = t.ItemCode

group by p1.PurchaseNo, p1.ItemCode

order by p1.ItemCode, sum(t.Qty) asc




select * From #tmp
where
ItemCode = 123 
and
Qty < 5 
union
select top 1 * From #tmp
where
ItemCode = 123 
and
Qty >= 5 
order by PurchaseNo desc

drop table #tmp

/* SQL SCRIPT END */

DECLARE @ItemCode int; 
DECLARE @AvailableQty int; 
SET @ItemCode = 123 ; 
SET @AvailableQty = (select QtyInStock from Stock where ItemCode = @ItemCode); 
SELECT 
    RowNum = ROW_NUMBER() OVER(ORDER BY PurchageNo),*   
INTO #PurchageTemp
FROM Purchage

DECLARE @MaxRownum INT;
SET @MaxRownum = (select COUNT(*)from #PurchageTemp);

DECLARE @Iter INT;
SET @Iter = 1;
DECLARE @QtySum int=0; 
DECLARE @QtySumTemp int=0; 
DECLARE @CurrentItem int; 
WHILE (@Iter <= @MaxRownum and @QtySum <= @AvailableQty)
BEGIN
set @QtySumTemp=@QtySum;
set @QtySumTemp = @QtySumTemp + (SELECT Qty FROM #PurchageTemp WHERE RowNum = @Iter and ItemCode=@ItemCode);
IF @QtySumTemp <= @AvailableQty 
BEGIN
set @QtySum=@QtySumTemp;
  set @CurrentItem= (SELECT PurchageNo FROM #PurchageTemp WHERE RowNum = @Iter and ItemCode=@ItemCode);
              insert into [Result] values (@CurrentItem);
END
              SET @Iter = @Iter + 1
END
DROP TABLE #PurchageTemp