Как реализовать FIFO в sql

Вам нужно создать хранимую процедуру в базе данных и взять количество из таблицы запасов. И вы также должны иметь идентификатор каждой записи, чтобы обновить эти записи, откуда вы взяли это количество.

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

Приведенный выше пример не компилируется, но вы можете получить логику, как вы можете получить записи из таблицы запасов в соответствии с методом FIFO. Вы можете использовать accept_date вместо ID в таблице RS_GIN_Master. но, я бы предпочел сделать его уникальным, так что, если я хочу получить конкретная запись тогда может быть возможна.

Один запрос .. вот так

Это должно быть настроено для вас, так как у вас есть группы и другие вещи, только для примера целей.

;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

И фиффл для него http://sqlfiddle.com/#! 6 / 9b063/34/0

Ниже должно работать для вас

Create table RS_GIN_Master
(id int,
 qty int,
 batch_no varchar(5),
 accept_date Datetime
 )
GO
Insert into RS_GIN_Master (id, qty, batch_no, accept_date)
values(1,10,'BT001','2018-04-06')
Insert into RS_GIN_Master (id, qty, batch_no, accept_date)
values(2,10,'BT002','2018-04-04')
Insert into RS_GIN_Master (id, qty, batch_no, accept_date)
values(3,15,'BT003','2018-05-06')
GO
----------------------------
CREATE PROC FIFO
 @TakenQty int
AS
BEGIN
 WITH cte AS (SELECT *, SUM(qty) OVER (ORDER BY accept_date, id ASC) as CumQty FROM RS_GIN_Master WHERE qty>0)
 SELECT TOP ((SELECT COUNT(*) FROM cte WHERE CumQty <@TakenQty)+1) batch_no, accept_date,
     CASE
          WHEN CumQty<@TakenQty THEN qty
              ELSE @TakenQty -(CumQty-Qty)
      END AS TakenOut
 FROM cte
END

Результат

| batch_no |          accept_date | TakenOut |
|----------|----------------------|----------|
|    BT002 | 2018-04-04T00:00:00Z |       10 |
|    BT001 | 2018-04-06T00:00:00Z |        5 |

Http://www.sqlfiddle.com/#! 18 / f7ee7/1