dcsimg
Results 1 to 15 of 15

Thread: First In, First Out subtraction from rows?

  1. #1

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,968

    Question First In, First Out subtraction from rows?

    I need to be able to compute the average cost of a certain item, not sure how to properly design the table as there is a need to subtract from the quantity of the item in First In, First Out order. Here's an example:

    Quantity Cost Total
    (a) 1000 100 100,000
    (b) 500 90 45,000

    The first row indicates the beginning balance of the item, and the next one is the next purchase. If we'll sum the quantity then well have 1,500 and the sum for the total is 145,000, and to get the Average Cost we'll do Total/Quantity which would be 145,000/1500 which will yield 96.66 as the Average Cost. Now, for every sales it must be first subtracted from (a) until it gets 0, then proceed to (b), and so on. I could add another column which would indicate the total deductions from the quantity per row but it does not seem to be the best design as I would need to check if it is already 0 before proceeding to the next row (b).

    Can anyone recommend a more efficient design such that I could subtract by row (until it gets zero)?

    TIA
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,528

    Re: First In, First Out subtraction from rows?

    Hmmm, reminds of the "net-worth"-calculation we have in the company i work for.
    Basically, we do it as follows:
    Each stock-location/item has its net-worth in a separate field beside the stock-quantity.
    To stay in your example: as long as we ship quantity from stock location "a" it's calculated according to the cost of that stock location (and subsequently subtracted from that stock-location).
    Meaning: A general total-"average" is calculated during runtime in realtime
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,845

    Re: First In, First Out subtraction from rows?

    Hard to suggest anything without more information.

    Why are you subtracting from "a" until it's "0", then start subtracting from "b", and so on...

    What is the purpose of this process?

  4. #4

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,968

    Re: First In, First Out subtraction from rows?

    Quote Originally Posted by wes4dbt View Post
    Hard to suggest anything without more information.

    Why are you subtracting from "a" until it's "0", then start subtracting from "b", and so on...

    What is the purpose of this process?
    It's a FIFO as I've already mentioned, "a" is the first so it has to be "zeroed" first before moving to the next "stock" which is "b", and so on.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,968

    Re: First In, First Out subtraction from rows?

    Quote Originally Posted by Zvoni View Post
    Hmmm, reminds of the "net-worth"-calculation we have in the company i work for.
    Basically, we do it as follows:
    Each stock-location/item has its net-worth in a separate field beside the stock-quantity.
    To stay in your example: as long as we ship quantity from stock location "a" it's calculated according to the cost of that stock location (and subsequently subtracted from that stock-location).
    Meaning: A general total-"average" is calculated during runtime in realtime
    Care to share an example?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,845

    Re: First In, First Out subtraction from rows?

    Quote Originally Posted by dee-u View Post
    It's a FIFO as I've already mentioned, "a" is the first so it has to be "zeroed" first before moving to the next "stock" which is "b", and so on.
    I know, you said that in your first post. You haven't added any clarification.

    What does each row represent? Is this an Inventory system or Stocks or ...... If this is an Inventory system then I'd say this is a bad design.

    It should have at least 2 Tables, 3 Tables would work well too. A simple example,

    2 Table design:

    Table 1: InventoryItems - Fields: ItemId ItemDescription Price Cost QuantityOnHand QuantityOnOrder

    Table 2: ItemTransactions - Fields: TransactionId ItemId Quantity Cost Total TransactionType (this field would indicate if this was a Purchase or a Sale)

    The 3 table method would breakup the ItemTransactions into ItemsPurchased and ItemsSold.

    It really depends on how much detailed history you want. If you don't care about Purchase or Sales history and the only thing you will ever want to know is the Average cost then you could use one table.

    Table 1: ItemTransactions - Fields: ItemId Quantity Cost Total QuantityBalance

    With this method QuantityBalance would initially be the same as Quantity and then you would subtract from QuantityBalance as items are sold.

  7. #7
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,423

    Re: First In, First Out subtraction from rows?

    Hi,

    I would go with the design form wes, the other thing will be when you add Stock again.

    No Company will let there Stock go to Zero, there is always a min.Quantity of Stock and some Query will execute and show the Amount on Stock

    regards
    Chris
    Last edited by ChrisE; Oct 21st, 2018 at 01:21 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,968

    Re: First In, First Out subtraction from rows?

    @wes4dbt

    Yes, it would be an inventory system. Those rows are the purchases, and there would be sales and those sales would have to be subtracted from those purchases FIFO. I generally am more concerned with the proper design of this before thinking of the other tables so I'm sorry if I didn't provide enough details. Yes, I am initially considering using another column as I have stated in my first post, perhaps call it "balance" as you also suggested. I'm just waiting for suggestions from the others who have dealt with the same or similar scenario. I could find examples on google but they are all calculated at runtime and I thought it would be tedious to always calculate the average cost of each item at runtime.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,528

    Re: First In, First Out subtraction from rows?

    Quote Originally Posted by dee-u View Post
    @wes4dbt

    *snipp*....and I thought it would be tedious to always calculate the average cost of each item at runtime.
    Why not just add a field to the Items-Master-Table called "AveragePrice"?
    Mind, you'd have to adjust this value with each transaction (purchase/sales) for each item.
    As for an example:

    Day 1
    Loc a 1000 pcs - 2$/pc - 2000$ worth
    Loc b 500 pcs - 2.50$/pc - 1250$ worth
    Average: 2.17$/pc (in runtime or written to this column i mentioned)

    Day 2 - Sold 300 pcs
    Loc a - 700 pcs - 2$/pc - 1400$ worth
    Loc b - 500 pcs - 2.50$/pc - 1250$ worth
    Average: 2.21$/pc

    Day 3 - Sold 500 pcs
    Loc a - 200 pcs - 2$/pc - 400$ worth
    Loc b - 500 pcs - 2.50$/pc - 1250$ worth
    Average: 2.36$/pc

    Day 4 - Sold 400 pcs
    Loc a - 0 - 2$/pc - 0$ worth
    Loc b - 300 pcs - 2.50$/pc - 750$ worth
    Average: 2.50$/pc

    Day 5 - Purchase 600 pcs
    Loc b - 300 pcs - 2.50$/pc - 750$worth
    Loc c - 600 pcs - 2.40$/pc - 1440$ worth
    Average: 2.43$/pc

    EDIT: There is a reason why we're using backwards-calculation from worth (instead of just using qty x price per location):
    Imagine now you do a stock inventory
    Day 6 - Stock Inventory (No Transactions with clients/suppliers)
    Loc b - Should be: 300 pcs - Found: 200 pcs (100 pcs stolen/lost whatever) - 2.50$/pc - 750$ worth
    Loc c - Should be: 600 pcs - Found: 600 pcs - 2.40$/pc - 1440$ worth

    Now for Loc b you could argue the new worth is 500$ (200pcs x 2.50$/pc).
    In our case: WRONG!
    The worth is still 750$ for the 200 pcs, because you paid an invoice to a supplier!
    So new average: 2.74$/pc

    EDIT2: I forgot:
    This "Backward"-Calculation could be implemented via
    Triggers on the Stock
    After-Insert --> Purchase
    After-Update --> Sales
    (After-Delete --> Sales - Quantity on Stock location runnig to zero)
    Last edited by Zvoni; Oct 22nd, 2018 at 01:46 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  10. #10

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,968

    Re: First In, First Out subtraction from rows?

    Yes, that's what I am considering. I'm just wondering if there was another design which wouldn't need another column to store that value. Thanks anyways guys, I guess it's settled but I will just leave this open just in case someone wants to suggest another way.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,528

    Re: First In, First Out subtraction from rows?

    It's the way how it's done in the company i work for, and our ERP is a professional industrial-grade Software, which passed any test of the IRS when they came calling to check everything is done according to law
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: First In, First Out subtraction from rows?

    I've had to deal with some fairly big FIFO inventory systems and I'd say there are 3 broad approaches. To some extent you can mix and match these if you want.

    1. Row per individual item. That means every single screw, sticker or widget gets an individual row in the table complete with purchase date, serial number etc. Note, there is no quantity field - the quantity is always 1. You will need some sort of "Order By" column (usually Purchase DateTime) in order to satisfy the FIFO requirement. This approach tends to work well in inventories where you have a small quantity of large items, eg a car dealership.
    2. Row per Purchase. NB this is a row per purchase by the inventory owner, not a row per sale to a customer. So it'd be keyed by part no (or similar) and would have a purchase datetime and quantity. This is a bit more pragmatic than approach 1 particular when dealing with large amounts of consumable items, eg paper, widgits and screws. It keeps the data reasonably small but does mean you lose track of an individual item (generally not an issue when dealing with consumables but should be considered).
    3. Current stock row only. Just maintain a single record for the current stock level of a given item and calculate the necessary values to meet the FIFO requirement. This may or may not be possible depending on exactly why you want the system to be FIFO. If it's just about being able to report stock values this can be achieved through weighted averages and by querying the purchase ledger but it can get tricksy and the value can start to drift over time due to issues like lost stock etc.

    Of the three 2 is usually the most pragmatic and looks like what you're thinking about from your sample data. It's pretty easy to implement and the only thing your sample data is missing is some kind of ordering field (purchase date, received date etc)
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,528

    Re: First In, First Out subtraction from rows?

    FD described it perfectly.

    Now the funny thing:
    The Company i work for is a trading company for screws, bolts, nuts, washers (Fasteners, you name it).

    FD,
    you psychic, or what?

    In our company it's kind of like this (very simplified and stripped down):

    Table StockLocations
    ID
    Coordinates (Aisle, Column, Level)
    SomeMoreFields

    Table ItemMaster
    PartNumber
    Description
    Weight per unit
    AveragePurchasePrice
    SomeMoreFields


    Now we have a m:n relationship between these two tables

    Table ItemOnStock
    StockID (Meaning Coordinates!)
    ItemID
    DateReceived
    QuantityOnStockLocation
    NetPurchaseWorth
    SomeMoreFields
    Last edited by Zvoni; Oct 22nd, 2018 at 06:24 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  14. #14
    Fanatic Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    873

    Re: First In, First Out subtraction from rows?

    @Zvoni: What are the keys on ItemOnStock table and other tables? You might be missing Lot column here, particularly if dealing with expiry dates, serial numbers and/or FIFO/LIFO/FEFO warehouses.

    @dee-u: For FIFO/LIFO/FEFO inventory you'll need to differentiate (a) and (b) in item's ledger by some unique (sequence) number. What I usually end up is a simple Lot column as a VARCHAR, with the actual information keyed in LotsInfo table -- delivery date, expiry date, etc.

    When the system has to fulfill an outbound inventory document it automatically selects (for each item) the Lot for (a) or for (b) based on warehouse setup -- preferring min delivery date for FIFO, max delivery date LIFO, min expiry date for FEFO.

    cheers,
    </wqw>

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,528

    Re: First In, First Out subtraction from rows?

    wq,

    (very simplified and stripped down)
    Yes, Lot/Batch-No (and whatever important information for backtracing items) have to be included.
    But no idea about the Keys (PK/FK), since i didn't write it, i just read it
    Oh Yeah, the whole thing runs on an OS/400 - DB2 with the clients being Windows-Clients in a Citrix-Environment.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width