How to implement FIFO in sql

Question

I am working on implementing FIFO in sql. I have a batch number concept in my application. Assuming that I am selling on inventory, then my application should tell me which inventory is the first. Let's. Say I bought inventory “A” on August 4th, August 5th and August 6th.

On 4th Aug - A Inventory has batch number BT002 - 10 (Qty) On 5th Aug - A Inventory has batch number BT003 - 15 (Qty) On 6th Aug - A Inventory has batch number BT001 - 10 (Qty) 

So, now I have a stock. Now in my hand the following:

 A Inventory BT002 - 10 - 4-Aug BT003 - 15 - 5-Aug BT001 - 10 - 6-Aug 

Now, if I want to sell this inventory to someone, then my application should tell me that I must sell BT002 (lot number) First, before it appears first.

It was a concept that I use in my application.

Now I want to sell 15 Qty from "A" (Inventory).

Then O / p should be as follows:

 BT002 - 10 BT003 - 5 

Here is my request:

 SELECT ISNULL(SUM(qty),0) AS Qty,batch_no,accept_date FROM RS_GIN_Master GROUP BY batch_no,accept_date HAVING ISNULL(SUM(qty),0) <= 15 ORDER BY accept_date asc 

O / p of this request:

How can I get O / P as follows:

 BT002 - 10 BT003 - 5 

Any help would be greatly appreciated. Thank you in advance.

Answer 1

This should work for you:
Working sample script

 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 

Just replace the marked part of the function with your request ...

Answer 2

You need to create a stored procedure in your database and take the quantity from your stock table. And you must also have the identifier of each record in order to update the records from where you got this number.

 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 

The above example is not compiled, but you can get the logic of how you can retrieve the entries from the stock table according to the FIFO method. You can use accept_date instead of the ID in the RS_GIN_Master table. but I would prefer to make it unique, so if I want to get a specific record, then it will be possible.

Answer 3

One request .. like this

This should be changed for you, since you have groups and other things, just for an example.

 ;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 lr on q.rn+1 = r.rn where q.remainder!=0 ) select * from q where used != 0 

and fiffle for it http://sqlfiddle.com/#!6/9b063/34/0