问如何在sql中实现FIFO

EN

CREATE FUNCTION [dbo].[GetBatchAmounts]
(
    @requestedAmount int

)
RETURNS 
@tBatchResults TABLE 
(   
    Batch nvarchar(50),
    Amount int
)
AS
BEGIN
    /*This is just a mock of ersults of your query*/
    DECLARE @RS_GIN_Master TABLE( 

     Qty int,
     batch_no NVARCHAR(max),
     accept_date DATETIME
    )

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT002', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(4 AS varchar) AS DATETIME)

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT003', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(5 AS varchar) AS DATETIME)

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT001', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(6 AS varchar) AS DATETIME)
    /*---------------------------*/

     DECLARE @Qty int
     DECLARE @batch_no NVARCHAR(max)
     DECLARE @accept_date DATETIME


    DECLARE myCursor CURSOR FOR

    SELECT Qty, batch_no, accept_date FROM @RS_GIN_Master ORDER BY accept_date ASC

    OPEN myCursor

    FETCH NEXT FROM myCursor INTO  @Qty, @batch_no,@accept_date

    WHILE (@@FETCH_STATUS = 0 AND @requestedAmount > 0 ) 
    BEGIN

        Declare @actualQty int
        IF @requestedAmount > @Qty
            SET @actualQty = @Qty
        ELSE    
            SET @actualQty = @requestedAmount


        INSERT INTO @tBatchResults (batch, Amount)
        SELECT @batch_no, @actualQty

        set @requestedAmount  = @requestedAmount - @actualQty

        FETCH NEXT FROM myCursor INTO @Qty, @batch_no,@accept_date

    END /*WHILE*/

    CLOSE myCursor
    DEALLOCATE myCursor

    RETURN
END

Alter PROCEDURE sp_UpdateStockForSale
    @batchNO varchar(10), 
    @qty decimal(9,3)
AS
BEGIN
    Create Table #tmpOutput(ID int identity(1,1), StockID int, batchNo varchar(10), qty decimal(9,3));
    SET NOCOUNT ON;

    DECLARE @ID int;
    DECLARE @Stock Decimal(9,3);

    DECLARE @TEMPID int;
    Select @TEMPID=(Max(ID)+1) From RS_GIN_Master Where qty > 0 And batch_no = @batchNO;

    While (@qty > 0) BEGIN
        Select @ID=ID, @Stock=qty From RS_GIN_Master Where qty > 0 And batch_no = @batchNO AND ID < @TEMPID Order By accept_date Desc;

        --If Outward Qty is more than Stock
        IF (@Stock < @qty) BEGIN
            SET @qty = @qty - @Stock;
            SET @Stock = 0;
        END
        --If Outward Qty is less than Stock
        ELSE BEGIN          
            SET @Stock = @Stock - @qty;
            SET @qty = 0;
        END    
        Insert Into #tmpOutput(StockID,batchNo,qty)Values(@ID,@batchNO,@Stock);
        SET @TEMPID = @ID;
        --This will update that record don't need it now.
        --Update RS_GIN_Master Set qty = @Stock Where ID=@ID
    END
    Select StockID, batchNo, qty From #tmpOutput;
END
GO

;with qty as (
  select 15 as value
)
,l as (
  select 
    ROW_NUMBER () over (order by accept_date desc) rn
    ,*
  from xxx
)
,q as (
  select 
    batch_no
    ,accept_date
    ,case when value>qty then value-qty else 0 end as remainder
    ,case when value>qty then qty else value end as used
    ,rn
  from l
  cross join qty
  where rn=1
  union all
  select 
    r.batch_no
    ,r.accept_date
    ,case when q.remainder>r.qty then q.remainder-r.qty else 0 end  as remainder
    ,case when q.remainder>r.qty then r.qty else q.remainder end as used
    ,r.rn
  from q 
  join l r
  on q.rn+1 = r.rn
  where  q.remainder!=0
)
select * 
from q
where used != 0