Fifo Calculation Using SQL

Question

I am try to fit FIFO method on my Inventory Table Which 4 columns as Item Code, MFG Date, On Stock Qty, Order Qty

So when i get an order of an item Order qty column will be updated and it should be processed based on Earliest MFG Date and once the Stock is exhausted it should move to next MFG Date. How can this be done in SQL.

Welcome to SO please read [ask]. show sample data and expected output and what you have tried. thankd. — OldProgrammer, May 08 '20 at 18:06

Ram · Accepted Answer · 2020-05-11T16:04:33.513

May be this will solve your problem. If Not, Please explain your scenario more detail

Before Going to Actual Implementation Refer the links, How to Loop through the set of records and Cursor

Sample Data for Inventory Table:

Query For FIFO Basis

Declare @ReqOrderQty as int;  
Set @ReqOrderQty=45;   // Requested Qty to Update the Records

Declare
@ItemNo Varchar(10),
@MFGCode Varchar(10),
@StockQty int,
@OrderQty int;

Declare @Query Varchar(500);
Declare Records Cursor 
for Select ItemNo,MFGCode,StockQty,OrderQty from Inventory where ItemNo='1' and OrderQty <> StockQty order by MFGCode asc

OPEN Records
FETCH NEXT FROM Records INTO 
@ItemNo,
@MFGCode,
@StockQty,
@OrderQty;

WHILE @@FETCH_STATUS = 0
BEGIN
        IF @ReqOrderQty > @StockQty
    BEGIN
        Set @ReqOrderQty = @ReqOrderQty - @StockQty;
        Set @Query='Update Inventory set OrderQty=' +CAST(@StockQty as varchar(100))+' where ItemNo='''+@ItemNo +'''and MFGCode='''+@MFGCode+''''
    END
    Else
    BEGIN
        Set @ReqOrderQty = @ReqOrderQty % @StockQty;
        Set @Query='Update Inventory set OrderQty=' +CAST(@ReqOrderQty as varchar(100))+' where ItemNo='''+@ItemNo +'''and MFGCode='''+@MFGCode+''''
    END
    PRINT @Query
    Exec (@Query)

    FETCH NEXT FROM Records INTO 
        @ItemNo,
        @MFGCode,
        @StockQty,
        @OrderQty;
END;

CLOSE Records;

DEALLOCATE Records;

Output

String Split

create table #Temp(value varchar(10))
Declare @ReqOrderQty Varchar(200)
Set @ReqOrderQty = '200,40,10,100,150';
INSERT INTO #Temp SELECT * FROM  STRING_SPLIT ( @ReqOrderQty , ',' )
 // Perform the Cursor Operation as mentioned above
Drop Table #Temp

Accept the Answer, If it helps you

Thanks for the Quick help. Helped to an extend. My main requirement was to find out the stock balance of each item from Mfg Date lot. If we get an order will go with date wise ascending order. or eg If Item A is having 3 mfg date lot and each lot has 5 units , When i get order of 12 units its should run through all lots on ascending order and update the balance of each lot in a column.so here i got order of 12 units so so 1 and 2 lots gets exhausted and balance 2 will be taken form 3 rd lot and the balance 3 should be updated for that row.Not sure if u can see my posted result pic — Sherin Narayanan, May 11 '20 at 07:28
Hi Ram Here we are declaring only one @ReqOrderQty. What if have multiple orders?? how we can declare it? — Sherin Narayanan, May 11 '20 at 10:16
@SherinNarayanan, We can use the String Split in sql server ,temp table, and Cursor to achieve our needs. Update the Code as well. — Ram, May 11 '20 at 15:59
String Split worked out. One Issue i got is Query is not moving to next condition. Its getting stopped at 1st one. Its not moving through the rows. I Mean query gets stooped before the 2nd Begin command — Sherin Narayanan, May 11 '20 at 16:53

Fifo Calculation Using SQL

1 Answers1