FEFO query if condition

  • We can insert a new row with the same CODART 'M0001' but with a different CODMVT and NUMLOT. I make a stock management by batch number. the data that I have provided on the table "FAIRE" are stock entries . What I want now is the query that will allow me to do outputs of stock from my table "FAIRE". If the user want to get out of a stock item amount (QTEMVT) M0001 must make its exit from the batch number (NUMLOT) M0001 which the date of the earliest expiration and if the amount out exceeds QTEMVT of M0001 then released unlike M0001 which has a different NUMLOT the previous and whose expiration date automatically preceding.

  • 136romy (2/5/2015)


    We can insert a new row with the same CODART 'M0001' but with a different CODMVT and NUMLOT. I make a stock management by batch number. the data that I have provided on the table "FAIRE" are stock entries . What I want now is the query that will allow me to do outputs of stock from my table "FAIRE". If the user want to get out of a stock item amount (QTEMVT) M0001 must make its exit from the batch number (NUMLOT) M0001 which the date of the earliest expiration and if the amount out exceeds QTEMVT of M0001 then released unlike M0001 which has a different NUMLOT the previous and whose expiration date automatically preceding.

    So what you mean is somebody wants to take some amount of M0001 out of stock and you are looking for an update query that can do that while pulling available amounts from the oldest rows first? I might make a suggestion here. Instead of updating the original amount I would add a second for AmountAllocated or AmountDisbursed or whatever. That way you will always know what the original quantity was. I can guarantee you that at some point somebody will ask how much of batch number such and such did we get in? If you decrement that amount it would be difficult or maybe impossible to be certain. I am swamped at work at the moment but if nobody else jumps in here to help I will try to knock something up over the weekend.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • that's exactly what I want to do but I could not find the right words to express myself. thank you in advance for your help

  • @sean, @136rony

    Sean you are a * for trying to help, Rony, It is hard when english is not your first language.

    There is a subtle difference between FIFO and FEFO. FIFO just takes stock from the oldest batch, With FEFO you may have old stock that can not be picked because the batch has expired (effectively the stock does not exist for picking purposes).

    If the application is not keeping track of what stock is still available from each batch then the only way to determine the 'available' stock is to keep a running count.

    Given the following batches:

    Batch A: Qty 10, Expires 10th

    Batch B: Qty 10, Expires 12th,

    Batch C: Qty 10, Expires 15th

    Order 1: Qty 6, Picked on 8th = 6 from Batch A. (A has 4 remaining)

    Order 2: Qty 6, Picked on 10th = 4 from Batch A (not enough stock left from A to complete order) plus 2 from Batch B (B has 8 remaining)

    Order 3: Qty 6, picked on 12th = 6 from Batch B (B has 2 remaining)

    Order 4: Qty 6, Picked on 14th = 6 from Batch C (B has expired and therefore the 2 remaining in stock are effectively scrap, C has 4 remaining)

    I have not read all the posts in the thread in detail, but it seems that there are problems with the database tables but also probably with the application itself. I would really advise you to have an allocations table that details for every order line which batch(es) were allocated to the line. Storing this data persistently rather than deriving it on the fly is much simpler code and less prone to errors (e.g. if the sequence in which orders are considered for the running counts, the derived allocations would change!)

    Seeing as you are stocking and managing medical products, traceability is going to be REALLY important so I would be pressing management to get the software to do a hard allocation rather than the derived allocation you seem to be seeking. I would also be writing scrap allocation records for the expired stock so that there is no possibility of it being picked in error - this scrap allocation would form the basis of physical stock removal instructions.

  • hi aaron,

    I see you understands the FEFO system. Can you help me with my query?

  • I thought I just did 😛

    Without a physical record of the which orders were assigned from which batches, you have to calculate the allocations right from the beginning of the data: I recenty had to do this for running balances on some 35K tenancy accounts and it took 4 days to run for 12 years worth of data.

    I would be looking to write some data to additional tables that look at the last allocation date in the allocations table and starts from that point, iterating through each day to allocate stock from unexpired batches and then cancel out any expired stock that has not been allocated.

    We would need full DDL and DML for the existing records.

  • hi,

    isn't there anyone to help me

  • as posted above

    "We would need full DDL and DML for the existing records."

    give us something to work with and you may be surprised at the quality of the answers

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I already post data. I do not know exactly what you are asking. Could you enlighten me please?

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply