Implementation of FIFO logic

Question

In a fairly simple database (Firebird 3.0) I am having an inventory table with fields related to products:

Purchase

QTY     PRICE   VALUE    Date
10      10      100      1.12.2018
25      9       225      5.12.2018
30      8       240      12.12.2018
50      7       350      14.12.2018
100     6       600      22.12.2018

Total (QTY, PRICE, VALUE): 215, 40, 1515

Here, the SOLD QTY is 150 and I need to sum(value) for the earliest records in "PURCHASE" until the sum(QTY) = SOLD QTY.

Exactly what is your expected output? Also what happens if the sum doesn't become exactly 150 (given that is not the case for the values you provided, you can either sum to 115 or to 215). — Mark Rotteveel, Dec 26 '18 at 09:30
Please read the instructions in the [SQL tag info](https://stackoverflow.com/tags/sql/info) about how to post a good SQL question and [edit] your question accordingly. — Zohar Peled, Dec 26 '18 at 09:33
@edlink I feel that your very approach is wrong. I feel that you try to implement some kind of catch-up processing. Then you really have to do a dedicated FIFO query, not to simulate it by scanning the whole of the all purchases history and summing up all the rows from the Creation Day up to today. It will, I believe, snowball in few years and get your program into DailyWTF. Read my discussions with Helena and see if my concerns and ideas from it apply to your case too. https://stackoverflow.com/questions/53750647 — Arioch 'The, Dec 26 '18 at 10:12
I agree with @Arioch'The , you really want to store what happened when the transaction occurs. Ex. "we sold 15 units at $15/each. Cost was 10 at $9 and 5 at $10" At the least, store the average or extended cost at that time. I've seen it done this way in a production ERP. [Hopefully this is something you can control.] — nater, Dec 26 '18 at 18:21

score 2 · Answer 1 · answered Dec 26 '18 at 11:00

This can easily be solved using Windowed Aggregates, supported in Firebird 3:

select *
  ,case when cumulative_qty <= sold_qty then qty
        when sold_qty-(cumulative_qty - qty) < 0 then 0
        else sold_qty-(cumulative_qty - qty)
   end as qty_sold
  ,case when cumulative_qty <= sold_qty then qty
        when sold_qty-(cumulative_qty - qty) < 0 then 0
        else sold_qty-(cumulative_qty - qty)
   end * price as value_sold
from
 (
   select *
     ,150 as sold_qty
      -- cumulative sum of quantity in stock (FIFO)
     ,sum(qty) over (order by date rows unbounded preceding) as cumulative_qty
   from tab
 ) as dt
;

Now you can calculate the sum:

select 
  sum(case when cumulative_qty <= sold_qty then qty
           when sold_qty-(cumulative_qty - qty) < 0 then 0
           else sold_qty-(cumulative_qty - qty)
      end) as qty_sold
  ,sum(case when cumulative_qty <= sold_qty then qty
            when sold_qty-(cumulative_qty - qty) < 0 then 0
            else sold_qty-(cumulative_qty - qty)
       end * price) as value_sold
from
 (
   select *
     ,150 as sold_qty
      -- cumulative sum of quantity in stock (FIFO)
     ,sum(qty) over (order by date rows unbounded preceding) as cumulative_qty
   from tab
 ) as dt
;

See db<>fiddle (using Postgres, but Firebird syntax should be the same)

Implementation of FIFO logic

1 Answers1