Hi,
I want to implement FIFO method for inventory in SQL.
I want to know how to manage the stock.
example when item received as item Apple 15 qty @ 14 rate, and apple already available in stock as 2 qty @ 12 rate.
once received apple 15 qty then we need to insert new row in stock table and add that 15 qty and rate @ 12? or change the qty 2+15=17 qty and what will be the rate?
Kindly help on this.
regards,
Basit
You should realise that the stock is the stock. When you buy apples it's not stock, it is a purchase order and when you sell items it's a sales order. So in this case you have 2 purchase orders:
Purchase order
Sales order
You don't know exactly what apples are sold, it can be the first or the second order. You have First In, First Out system to calculate. So the stock is 2 qty 12 + 15 qty 14= 24 + 210 = 234.
If you had LIFO system, Last in, First Out then it would be 15 qty x 12 rate + 2 x 14 rate = 180 + 28 = 208.
It is complex when you create a stock table, it means you should recalculate the stock each time as you can have multiple purchase orders and you cannot be sure that all purchase orders and sales orders are in the right sequence. For a good SQL example you can find a great article here:
Many Thanks for your reply, i want to for manufacturing system, where finished good and inventory in one table that's why want to use stock table.
example, when material received from supplier i want to add in stock table, when material issue to the production then update stock table, when doing sales then update stock table, also once received the material track the expiry date also.
Is this is the good way to do or without stock table get the balance qty and rate as per you suggest the link?
regards,
Basit.
It's really hard to maintain a stock table with FIFO, I would avoid it if possible. If you want a stock-table you should think about a procedure to recalculate the rates. As explaned, you can not be sure that the data in your system is in the correct sequence. For example, what if material from supplier is not correct and send back?
Hello,
You can control the FIFO mechanism if you "pair" the In and Out transactions.
So you should add for each output transaction the Id of the input. That also means you should split the outputs if (as in the example above) the output qty is 23: you split it into 15 paired with the first input and 8 paired wth the latter.
Thus you can have the "residual" stock for each entry.
Many thanks for your help, really appreciated.
Thank you very much for your input. Are you saying in stock table, each item received enter new row with unique ID, is that correct?
example ItemCode A , received date 01/01/2023 received 10 qty rate 15
ItemCode A, received date 02/02/2023 received 11 qty rate 14
then in stock table add two rows with unique ID?
The stock is a static value resulting from in/out transactions. You can't have a stock table but a transaction table and then calculate the stock at different datetimes.
I woul add two columns (not rows) for each row: an unique identifier (Id) -this one usually is already in place as primary key- and another id: EntryId. The last one should be null for input transactions and, for the output transactions, should keep the unique Id of the input.
As for your example, if you have an output of 15 pcs on 05/02/2023, the table should look like this
| Id | EntryId | Type | Moment | Qty | Price |
| 1 | Null | Input | 01/01/2023 | 10 | 15 |
| 2 | Null | Input | 02/02/2023 | 11 | 14 |
| 3 | 1 | Output | 05/02/2023 | 10 | 15 |
| 4 | 2 | Output | 05/02/2023 | 5 | 14 |
Many thanks for prompt reply.
what i understood is instead of maintaining stock table i need to used transactions table such as Good Received Note [GRN] and Good Issue Note [GIN] tables to calculate the stock. Is that correct?
I once again say there's no such thing as "stock table"; the stock is the result (Sum(Sign*Qty)) of transactions.
So you will have a Products table and a Transactions table (add ProductId to the table I posted).
That's a very basic structure. Actually I think you need at least four tables:
Many thanks once again. Im planning to use following tables
i mention only footer tables
Mst_Item/Mst_Product
Trn_GoodReceiveNoteDetails
Trn_GoodIssueNoteDetails
based on above table will get the current stock Is this structure is correct?
Using those tables you can calculate the stock at various moments of time.
If you need FIFO you need the mechanism I mentioned earlier (EntryId).
However, these are application considerations that depend on you and not SQL issues
Thanks for this useful information.