Calculate out price in FIFO SQL

Question

Using Postgres 11

Using FIFO, i would like to calculate the price of items taken from the inventory, to keep track of the value of the total inventory.

Dataset is as follows:

ID  | prodno | amount_purchased | amount_taken | price | created_at
uuid  13976    10                 NULL           130     <timestamp>  
uuid  13976    10                 NULL           150     <timestamp>
uuid  13976    10                 NULL           110     <timestamp>
uuid  13976    10                 NULL           100     <timestamp>
uuid  13976    NULL                 14           ??      <timestamp>

Before inserting the row with amount_taken i would need to calculate what the avg price of each of the 14 items is, which in this case would be 135,71, but how to calculate this relatively efficient?

My initial idea was to delegate the rows into two temp tables, one where amount_taken is null, and one where it is not null, and then calculate all the rows down, but seeing as this table could become rather large, rather fast (since most of the time, only 1 item would be taken from the inventory), i worry this would be a decent solution in the short term, but would slow down, as the table becomes larger. So, what's the better solution internet?

Fogle

Answer 1

Given this setup:

CREATE TABLE test (
    id int
    , prodno int 
    , quantity numeric
    , price numeric 
    , created_at timestamp
);
INSERT INTO test VALUES
    (1, 13976, 10,    130, NOW())
    , (2, 13976, 10,  150, NOW()+'1 hours')
    , (3, 13976, 10,  110, NOW()+'2 hours')
    , (4, 13976, 10,  100, NOW()+'3 hours')
    , (5, 13976, -14, NULL, NOW()+'4 hours')
    , (6, 13976, -1, NULL, NOW()+'5 hours')
    , (7, 13976, -10, NULL, NOW()+'6 hours')
    ;

then the SQL

SELECT id, prodno, created_at, qty_sold
    -- 5
    , round((cum_sold_cost - coalesce(lag(cum_sold_cost) over w, 0))/qty_sold, 2) as fifo_price 
    , qty_bought, prev_bought, total_cost
    , prev_total_cost
    , cum_sold_cost
    , coalesce(lag(cum_sold_cost) over w, 0) as prev_cum_sold_cost
FROM (
    SELECT id, tneg.prodno, created_at, qty_sold, tpos.qty_bought, prev_bought, total_cost, prev_total_cost
        -- 4
        , round(prev_total_cost + ((tneg.cum_sold - tpos.prev_bought)/(tpos.qty_bought - tpos.prev_bought))*(total_cost-prev_total_cost), 2) as cum_sold_cost 
    FROM (
      SELECT id, prodno, created_at, -quantity as qty_sold
          , sum(-quantity) over w as cum_sold
      FROM test
      WHERE quantity < 0
      WINDOW w AS (PARTITION BY prodno ORDER BY created_at)
    -- 1
    ) tneg 
    LEFT JOIN (
      SELECT prodno
          , sum(quantity) over w as qty_bought
          , coalesce(sum(quantity) over prevw, 0) as prev_bought
          , quantity * price as cost                              
          , sum(quantity * price) over w as total_cost
          , coalesce(sum(quantity * price) over prevw, 0) as prev_total_cost
      FROM test
      WHERE quantity > 0
      WINDOW w AS (PARTITION BY prodno ORDER BY created_at)
          , prevw AS (PARTITION BY prodno ORDER BY created_at ROWS BETWEEN unbounded preceding AND 1 preceding)
    -- 2
    ) tpos 
    -- 3
    ON tneg.cum_sold BETWEEN tpos.prev_bought AND tpos.qty_bought 
        AND tneg.prodno = tpos.prodno
    ) t
WINDOW w AS (PARTITION BY prodno ORDER BY created_at)

yields

| id | prodno | created_at                 | qty_sold | fifo_price | qty_bought | prev_bought | total_cost | prev_total_cost | cum_sold_cost | prev_cum_sold_cost |
|----+--------+----------------------------+----------+------------+------------+-------------+------------+-----------------+---------------+--------------------|
|  5 |  13976 | 2019-03-07 21:07:13.267218 |       14 |     135.71 |         20 |          10 |       2800 |            1300 |       1900.00 |                  0 |
|  6 |  13976 | 2019-03-07 22:07:13.267218 |        1 |     150.00 |         20 |          10 |       2800 |            1300 |       2050.00 |            1900.00 |
|  7 |  13976 | 2019-03-07 23:07:13.267218 |       10 |     130.00 |         30 |          20 |       3900 |            2800 |       3350.00 |            2050.00 |

---

1. tneg contains information about quantities sold

   | id | prodno | created_at                 | qty_sold | cum_sold |
   |----+--------+----------------------------+----------+----------|
   |  5 |  13976 | 2019-03-07 21:07:13.267218 |       14 |       14 |
   |  6 |  13976 | 2019-03-07 22:07:13.267218 |        1 |       15 |
   |  7 |  13976 | 2019-03-07 23:07:13.267218 |       10 |       25 |
   

2. tpos contains information about quantities bought

   | prodno | qty_bought | prev_bought | cost | total_cost | prev_total_cost |
   |--------+------------+-------------+------+------------+-----------------|
   |  13976 |         10 |           0 | 1300 |       1300 |               0 |
   |  13976 |         20 |          10 | 1500 |       2800 |            1300 |
   |  13976 |         30 |          20 | 1100 |       3900 |            2800 |
   |  13976 |         40 |          30 | 1000 |       4900 |            3900 |
   

3. We match rows in tneg with rows in tpos on the condition that cum_sold is between qty_bought and prev_bought. cum_sold is the cumulative amount sold, qty_bought is the cumulative amount bought, and prev_bought is the previous value of qty_bought.

   | id | prodno | created_at                 | qty_sold | cum_sold | qty_bought | prev_bought | total_cost | prev_total_cost | cum_sold_cost |
   |----+--------+----------------------------+----------+----------+------------+-------------+------------+-----------------+---------------|
   |  5 |  13976 | 2019-03-07 21:07:13.267218 |       14 |       14 |         20 |          10 |       2800 |            1300 |       1900.00 |
   |  6 |  13976 | 2019-03-07 22:07:13.267218 |        1 |       15 |         20 |          10 |       2800 |            1300 |       2050.00 |
   |  7 |  13976 | 2019-03-07 23:07:13.267218 |       10 |       25 |         30 |          20 |       3900 |            2800 |       3350.00 |
   

4. The fraction

   ((tneg.cum_sold - tpos.prev_bought)/(tpos.qty_bought - tpos.prev_bought)) as frac
   

   measures how far cum_sold lies in between qty_bought and prev_bought. We use this fraction to compute cum_sold_cost, the cumulative cost associated with buying cum_sold items. cum_sold_cost lies frac distance between prev_total_cost and total_cost.

5. Once you obtain cum_sold_cost, you have everything you need to compute marginal FIFO unit prices. For each line of tneg, the difference between cum_sold_cost and its previous value is the cost of the qty_sold. FIFO price is simply the ratio of this cost and qty_sold.

Initiation

Thank you for the very complete answer. From the way you structure the query it seems you might have solved future issues i would have had too (fractions of code can most certainly be reused for other queries) – Fogle

If I am not mistaken it is not correct to use BETWEEN when joining tneg and tpos. It should be tneg.cum_sold > tpos.prev_bought AND tneg.cum_sold <= tpos.qty_bought. For example if you change quantity in row with id 6 to 6 you will get two joins on one row. But also thank you for this answer it helped me a lot. – Wreckful