0

nice day for all. I spent more than three days of searching and testing but nothing helped me. let us assume I have many way to enter items of product to storages, So I created 'view' to show me the product transactions "in" like this:

product_id storage_id type unit_cost quantity date
82 1 in 1 10 2022-04-25 17:04:13
82 1 in 2 10 2022-04-25 17:04:51
82 1 in 3 10 2022-04-25 17:07:13
82 16 in 2 10 2022-04-25 17:10:51
82 16 in 3 10 2022-04-25 17:12:13

also I sold items by orders (order and order items tables view)

product_id storage_id quantity date
82 1 15 2022-04-25 17:06:27
82 1 7 2022-04-25 17:08:24
82 2 5 2022-04-25 17:20:13

so from these tables and views, is there any query to calc my inventory valuation (cost of goods didn't sold) based on FIFO and LIFO that query should show data as follow(FIFO):

product_id storage_id remaining_quantity total_cost
82 1 8 24
82 16 15 40

(LIFO):

product_id storage_id remaining_quantity total_cost
82 1 8 14
82 16 15 35
Anas deve
  • 11
  • 6
  • Having worked with complete accounting systems in the past dealing with inventory, sales, COGS, LIFO/FIFO valuations, there was always an additional table that accounted for how many of a product's inbound counts were applied to which orders so you could get the decreased counts before going into the next inventory's quantity basis. Trying to just for a query now without further handling for later tracking might be a mistake. Inventory shrinkage (theft / damage), physical inventory count corrections, etc. Dont stop until you consider those moving forward. – DRapp Apr 26 '22 at 02:44
  • @DRapp thank for your advice, in fact I am working on already programmed system and I need to modify it and add some features. there is table as you mentioned but these tables calc unit cost based on cost method for each product(LIFO,FIFO, AVG...) so these table can not help me because I must tracking all operations from beginning to find total cost for each product,storage regardless of cost method specified to product – Anas deve Apr 26 '22 at 03:19
  • 1
    what does `select version();` show? to do this efficiently you want window functions – ysth Apr 26 '22 at 03:40
  • Calculate cumulative sum of amounts for "in" table and total amount for "out" table. Then join and filter. PS. I assume that the data is consistent. – Akina Apr 26 '22 at 04:19
  • @Akina doesn't work that way, they want cost determined by most recent (for LIFO) or least recent (for FIFO) in transactions that make up the quantity sold from the date of each sale – ysth Apr 26 '22 at 10:26
  • @ysth For FIFO this works ideally in single query. For LIFO this needs in recursive CTE (and even client-side solving seems to be more reasonable). – Akina Apr 26 '22 at 12:00
  • @ysth it's 8.0.21 version – Anas deve Apr 26 '22 at 12:46

0 Answers0