Best Joins/Cursors for FIFO Valuation of Stocks
-
The Problem:
I want to do FIFO(First in First Out) valuation of Item Quantity Issue slips. In one table I have datewise records of Receipt Qty and Value. In another table I have datewise Issue records with Quantity. How to Link them in best possible way so that FIFO valuation can be done.
Pl. note that one receipt qty. may have multiple issues and one issue make need multiple receipts.
What is the best way?
-
Could you post a little DDL and sample data to help us out?
Eddie Wuerch
MCM: SQL -
From what I've seen, I think you want something like...
You receive goods - each received shipment has a quantity and a per item cost.
You consume goods - each time you consume you could consume a different amount. You want to consume the earliest received goods first. Thus you could, in one consumption, use goods from several shipments.
You could have a table for received goods such as
ReceivedID, ItemTypeID ReceivedDateTime QtyReceived QtyRemaining UnitCost
Then you could also create for each consumption such as
ConsumptionID, ItemTypeID ConsumptionDate ConsumptionQty TotalCost
All easy so far, except calculating TotalCost in the consumption...
For each consumption, you have a consumptionDetails table which contains
ConsumptionID, ReceivedID, Qty
To populate the consumptionDetails table you would take records from the received table, decrementing the qtyRemaining on each one. If not enough qty on the first, then set remaining to 0 and try to take the balance from the next received line, etc. As you go you can use the unit cost to keep a running total for TotalCost.
The way I've described it is using a cursor but it can easily be made set based with a correlated subquery - i'm just a bit tired to spell it out at the moment. If you are only doing this for a few rows the cursor will probably do even though it's not as "elegant"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply