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