sql server - How to implement FIFO in sql

Question

I working on FIFO implementation in sql. I have Batch number concept in my application. If suppose I am selling on inventory then my application should tell me that which inventory is the first come. Lets. Say I purchased Inventory 'A' on 4th-Aug, 5th-Aug & 6th-Aug

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

So, Now I am having stock Now in my hand as following :

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

Now If I want to sell that Inventory to anybody then my application should tell me that I should sell BT002 (Batch number) inventory first beacause it came first.

That was the concept I am using in my application.

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

Then O/p Should be like this :

BT002 - 10
BT003 - 5

Here's My Query :

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 Given Query :

How can I get O/P like this :

BT002 - 10
BT003 - 5

Any Help will be appreciated. Thank you in Advance.

Answer 1

You need to create a stored procedure in your database and taking the quantity from your stock table. And you should also have the id of each record to update that records from where u have taken that qty.

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 how you can retrieve the records from your stock table according to FIFO method. You can use accept_date instead of ID in RS_GIN_Master table. but, i would prefer to make it unique so, if i want to get a specific record then it can be possible.