MisterOctober
March 9, 2022
146 views
1 vote
2 Answers

Background

I need to implement inventory valuation / costing using the FIFO (first-in, first-out) method.
I’m running Postgres 11 running on CentOS 7.
I’ve looked at, and tried, a fair number of hypotheses from SO and the wider internet (as well as searching my own print library which includes SQL Queries for Mere Mortals, PostgreSQL Up & Running, The SQL Cookbook, Practical Issues In Database Management, and other quality reference works), and to date, I can’t find a solution that works for closing inventory valuation.
(I’ve also tried reasoning it out on my own, but have failed to come up with a plausible appraoch)

NOTE In my case, I have permission to change the table structure, etc, of the setup, so I can add / remove / change anything in the setup as needed (such as, e.g., adding a direction column to the movements table, as some approaches I’ve tried have indicated, changing queries, etc etc)

Current setup

I have a table mockup_inv_movements:

CREATE TABLE the_schema.mockup_inv_movements (
    id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    sku TEXT, 
    adjustment_quantity NUMERIC,
    unit_cost NUMERIC(19,2),
    po_num INTEGER
);

and this view mockup_inv_movements_with_fifo_cost adds FIFO cost for sale / ‘out’ rows, calculated from a query (shown later below):

CREATE VIEW the_schema.mockup_inv_movements_with_fifo_cost AS (
    select 
    i.id, 
    i.created_at, 
    i.po_num, 
    i.sku, 
    i.adjustment_quantity, 
    i.unit_cost, 
    m.fifo_unit_cost
    FROM 
    the_schema.mockup_inv_movements i 
    LEFT OUTER JOIN 
    the_schema.fifo_hypothesis_2_mockup m 
    ON 
    i.id = m.id 
    ORDER BY i.id 
);

Adding some test inventory movement data:

-- insert receipt / 'in' records
INSERT INTO the_schema.mockup_inv_movements (sku, adjustment_quantity, unit_cost, po_num, created_at )
VALUES ('foo_product',100,4,123, now()+'1 hour'), ('foo_product',10,3,987, now()+'2 hour'), ('foo_product',20,7,223, now()+'3 hours')
;

INSERT INTO the_schema.mockup_inv_movements (sku, adjustment_quantity, unit_cost, po_num, created_at )
VALUES ('bar_product',100,5,123, now()+'4 hours'),('bar_product',30,6,963, now()+'5 hours'),('bar_product',50,8,223, now()+'6 hours'),('bar_product',5,5,456, now()+'7 hours')
;
--insert sale / 'out' records 

INSERT INTO the_schema.mockup_inv_movements (sku, adjustment_quantity, unit_cost, po_num, created_at )
VALUES ('bar_product',-50,null,null, now()+'8 hours'),('bar_product',-30, null,null, now()+'9 hours'),
('bar_product',-20,null,null, now()+'10 hours'),('bar_product',-10,null,null, now()+'11 hours')
;

INSERT INTO the_schema.mockup_inv_movements (sku, adjustment_quantity, unit_cost, po_num, created_at )
VALUES ('foo_product',-70,null,null, now()+'12 hours'), ('foo_product',-5,null,null, now()+'13 hours'),
('foo_product',-20,null,null, now()+'14 hours'),('foo_product',-10,null,null, now()+'15 hours')
;

OK, now here’s the query that calculates the ‘sale/out’ price for each, taken from this question, which seems to work; note that I’m only pulling in the column fifo_unit_cost from this query at the moment:

CREATE VIEW the_schema.fifo_hypothesis_2_mockup AS (
    SELECT 
    id, 
    sku, 
    created_at AT TIME ZONE 'mst', 
    qty_sold,
        -- 5
    round((cumulative_sold_cost - coalesce(lag(cumulative_sold_cost) over w, 0))/qty_sold, 2) as fifo_unit_cost,
    qty_bought, 
    prev_bought, 
    total_cost,
    prev_total_cost,
    cumulative_sold_cost,
    coalesce(lag(cumulative_sold_cost) over w, 0) as prev_cumulative_sold_cost
    FROM (
        SELECT id, 
        tneg.sku, 
        created_at, 
        qty_sold, 
        tpos.qty_bought, 
        prev_bought, 
        total_cost, 
        prev_total_cost,
            -- 4
        round(prev_total_cost + ((tneg.cumulative_sold - tpos.prev_bought)/(tpos.qty_bought - tpos.prev_bought))*(total_cost-prev_total_cost), 2) as cumulative_sold_cost 
        FROM (
        SELECT 
            id, 
            sku, 
            created_at, 
            -(adjustment_quantity) as qty_sold,
            sum(-(adjustment_quantity)) over w as cumulative_sold
        FROM the_schema.mockup_inv_movements 
        WHERE adjustment_quantity < 0
        WINDOW w AS (PARTITION BY sku ORDER BY created_at)
        -- 1
        ) tneg 
        LEFT JOIN (
        SELECT
            sku,
            sum(adjustment_quantity) over w as qty_bought,
            coalesce(sum(adjustment_quantity) over prevw, 0) as prev_bought,
            adjustment_quantity * unit_cost as cost,                              
            sum(adjustment_quantity * unit_cost) over w as total_cost,
            coalesce(sum(adjustment_quantity * unit_cost) over prevw, 0) as prev_total_cost
        FROM the_schema.mockup_inv_movements 
        WHERE adjustment_quantity > 0
        WINDOW w AS (PARTITION BY sku ORDER BY created_at),
                prevw AS (PARTITION BY sku ORDER BY created_at ROWS BETWEEN unbounded preceding AND 1 preceding)
        -- 2
        ) tpos 
        -- 3
        ON 
        ((tneg.cumulative_sold > tpos.prev_bought )
        AND ( tneg.cumulative_sold <= tpos.qty_bought ))
        AND tneg.sku = tpos.sku
        ) t
    WINDOW w AS (PARTITION BY sku ORDER BY created_at)
    ORDER BY id
    )
    ;

Now here’s the part where I’m having trouble.
I need to calculate the value of remaining stock / inventory on hand, also known as "closing stock" or "closing inventory." I’ve tried a number of approaches including this question and this ‘set-based speed phreakery’ method, the latter of which I readily admit that I don’t fully comprehend,

The approach that has come closest to working for me is this older hypothesis from Ranjeet Rana, BUT although it does seem to assign the FIFO costs according to the correct breakdown, the sum of closing stock for each SKU does not seem to match the raw difference between ‘in’ and ‘out’ quantities.

Here’s the closing stock query adapted from Rana (comments mine; I left them in just in case they might indicate where my error is).

CREATE VIEW the_schema.closing_inv_hyp_3 AS (
    select *, 
        case 
        when cumulative>0 and adjustment_quantity>=cumulative -- note that sale/out adjustment_quantity / cumulative is always less than zero
        then cumulative*cost -- in this case, some amount of this row's receipt has been sold, and the remainder qty is shown in 'cumulative'
        when cumulative>0 and adjustment_quantity<cumulative 
        then adjustment_quantity*cost -- in this case, none of this row's receipt has been sold, and so the entire adjustment amount is multiplied by unit cost
        else 0 -- sale rows are assigned zero for this column
        end as closing_stock 
    from ( 
        select 
        *, -- all rows from subquery
        sum(adjustment_quantity) over (order by srl) as cumulative  -- THIS is the problematic column
        from (
            select 
            0 as srl,  -- this ensures that all 'sale / out' rows float to the top 
            id,
            sku, 
            adjustment_quantity, 
            COALESCE(fifo_unit_cost,unit_cost) AS cost, 
            created_at 
            from 
            the_schema.mockup_inv_movements_with_fifo_cost 
            where adjustment_quantity < 0 -- SALE / OUT only
            UNION -- gets all from both queries (less any dupes)
            select 
            row_number() over(order by created_at) as srl, -- this assigns a synthetic sequential row number to the 'PO / in' rows and ensures the are pushed to the bottom
            id,
            sku, 
            adjustment_quantity, 
            COALESCE(fifo_unit_cost,unit_cost) AS cost,  
            created_at  
            from 
            the_schema.mockup_inv_movements_with_fifo_cost  
            where 
            adjustment_quantity > 0 -- PO / IN only
            ORDER BY srl 
    )as tab
    ) as maintab
);

With this in place, we should be able to get the sum of closing stock value per SKU with:

SELECT 
        sku, 
        sum(closing_stock) as closing_stock_sum_value
        FROM 
        the_schema.closing_inv_hyp_3 
        WHERE closing_stock > 0
        GROUP BY sku
        ORDER by sku
    ;

However, as I mentioned, the totals do not match up with the basic inventory difference calculation (specifically in this test example, I would expect 75 units of bar_product to be represented in closing stock, whereas this query shows 100):

 srl | id  |     sku     | adjustment_quantity | cost |          created_at           | cumulative | closing_stock 
-----+-----+-------------+---------------------+------+-------------------------------+------------+---------------
   0 | 102 | foo_product |                 -70 | 4.00 | 2022-03-10 07:27:05.447572+00 |       -215 |             0
   0 | 100 | bar_product |                 -20 | 5.00 | 2022-03-10 05:27:05.447572+00 |       -215 |             0
   0 | 101 | bar_product |                 -10 | 6.00 | 2022-03-10 06:27:05.447572+00 |       -215 |             0
   0 | 103 | foo_product |                  -5 | 4.00 | 2022-03-10 08:27:05.447572+00 |       -215 |             0
   0 | 105 | foo_product |                 -10 | 3.50 | 2022-03-10 10:27:05.447572+00 |       -215 |             0
   0 |  99 | bar_product |                 -30 | 5.00 | 2022-03-10 04:27:05.447572+00 |       -215 |             0
   0 |  98 | bar_product |                 -50 | 5.00 | 2022-03-10 03:27:05.447572+00 |       -215 |             0
   0 | 104 | foo_product |                 -20 | 4.00 | 2022-03-10 09:27:05.447572+00 |       -215 |             0
   1 |  91 | foo_product |                 100 | 4.00 | 2022-03-09 20:27:05.447572+00 |       -115 |             0
   2 |  92 | foo_product |                  10 | 3.00 | 2022-03-09 21:27:05.447572+00 |       -105 |             0
   3 |  93 | foo_product |                  20 | 7.00 | 2022-03-09 22:27:05.447572+00 |        -85 |             0
   4 |  94 | bar_product |                 100 | 5.00 | 2022-03-09 23:27:05.447572+00 |         15 |         75.00
   5 |  95 | bar_product |                  30 | 6.00 | 2022-03-10 00:27:05.447572+00 |         45 |        180.00
   6 |  96 | bar_product |                  50 | 8.00 | 2022-03-10 01:27:05.447572+00 |         95 |        400.00
   7 |  97 | bar_product |                   5 | 5.00 | 2022-03-10 02:27:05.447572+00 |        100 |         25.00
(15 rows)

It seems like this would be the kind of thing that has a more-or-less standardized solution, but so far none of the resources I’ve found / tried has guided me to a working approach.

How can I accurately do FIFO closing stock / inventory valuation in Postgres?
All guidance much appreciated!

Answers

Using "Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem" as an example, re-working that approach for Postgres and the change of table/columns produces this query:

/* Sum up the ins and outs to calculate the remaining stock level */
WITH    cteStockSum
  AS ( SELECT   sku ,
                SUM(adjustment_quantity) AS TotalStock
       FROM     mockup_inv_movements
       GROUP BY sku
     )
, cteReverseInSum
  AS ( SELECT  s.sku ,
               s.created_at ,
                ( SELECT SUM(i.adjustment_quantity)
                 FROM   mockup_inv_movements AS i 
                 WHERE     i.sku = s.sku
                           AND i.adjustment_quantity > 0
                           AND i.created_at >= s.created_at
               ) AS RollingStock ,
               s.adjustment_quantity AS ThisStock
       FROM    mockup_inv_movements AS s
       WHERE   s.adjustment_quantity > 0
     )
/* Using the rolling balance above find the first stock movement in that meets 
   (or exceeds) our required stock level */
/* and calculate how much stock is required from the earliest stock in */
, cteWithLastTranDate
  AS ( SELECT   w.sku ,
                w.TotalStock ,
                LastPartialStock.created_at ,
                LastPartialStock.StockToUse ,
                LastPartialStock.RunningTotal ,
                w.TotalStock - LastPartialStock.RunningTotal
                + LastPartialStock.StockToUse AS UseThisStock
       FROM     cteStockSum AS w
                CROSS JOIN LATERAL ( SELECT
                                    z.created_at ,
                                    z.ThisStock AS StockToUse ,
                                    z.RollingStock AS RunningTotal
                              FROM  cteReverseInSum AS z
                              WHERE z.sku = w.sku
                                    AND z.RollingStock >= w.TotalStock
                              ORDER BY  z.created_at DESC
                              LIMIT 1
                            ) AS LastPartialStock
     )
/*  Sum up the cost of 100% of the stock movements in after the returned stockid and for that stockid we need 'UseThisStock' items' */
SELECT  y.sku ,
y.TotalStock AS CurrentItems ,
SUM(CASE WHEN e.created_at = y.created_at THEN y.UseThisStock
         ELSE e.adjustment_quantity
    END * Price.unit_cost) AS CurrentValue
FROM    cteWithLastTranDate AS y
INNER JOIN mockup_inv_movements AS e
    ON e.SKU = y.SKU
       AND e.created_at >= y.created_at
       AND e.adjustment_quantity > 0
CROSS JOIN LATERAL ( 
/* Find the Price of the item in */ SELECT
                                    p.unit_cost
                            FROM mockup_inv_movements AS p 
                            WHERE   p.SKU = e.SKU
                                    AND p.created_at <= e.created_at
                                    AND p.adjustment_quantity > 0
                            ORDER BY p.created_at DESC
                            LIMIT 1
            ) AS Price
GROUP BY y.sku ,y.TotalStock
ORDER BY y.sku

and from your sample data the result produced is this:

+-------------+--------------+--------------+
|     sku     | currentitems | currentvalue |
+-------------+--------------+--------------+
| bar_product |           75 |       545.00 |
| foo_product |           25 |       155.00 |
+-------------+--------------+--------------+

also see: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f564a6cfda3374c2057b437f845a4bdf

2023 shorter version using postgres 15 and window function

I comment the previous code, you can compare it, or delete to see

ref to this link: https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/t-sql-window-function-speed-phreakery-the-fifo-stock-inventory-problem/



/* Sum up the ins and outs to calculate the remaining stock level */
WITH    cteStockSum
  AS ( SELECT   sku ,
                SUM(adjustment_quantity) AS TotalStock
       FROM     the_schema.mockup_inv_movements
       GROUP BY sku
     )
, cteReverseInSum
  AS ( SELECT  s.sku ,
               s.created_at ,
--                 ( SELECT SUM(i.adjustment_quantity)
--                  FROM   the_schema.mockup_inv_movements AS i 
--                  WHERE     i.sku = s.sku
--                            AND i.adjustment_quantity > 0
--                            AND i.created_at >= s.created_at
--                ) AS RollingStock ,
               sum(s.adjustment_quantity) over (partition by sku order by s.created_at  rows between current row and unbounded following) as RollingStock,
               s.adjustment_quantity AS ThisStock
       FROM    the_schema.mockup_inv_movements AS s
       WHERE   s.adjustment_quantity > 0
     )
/* Using the rolling balance above find the first stock movement in that meets 
   (or exceeds) our required stock level */
/* and calculate how much stock is required from the earliest stock in */
, cteWithLastTranDate
--   AS ( SELECT   w.sku ,
--                 w.TotalStock ,
--                 LastPartialStock.created_at ,
--                 LastPartialStock.StockToUse ,
--                 LastPartialStock.RunningTotal ,
--                 w.TotalStock - LastPartialStock.RunningTotal
--                 + LastPartialStock.StockToUse AS UseThisStock
--        FROM     cteStockSum AS w
--                 CROSS JOIN LATERAL ( SELECT
--                                     z.created_at ,
--                                     z.ThisStock AS StockToUse ,
--                                     z.RollingStock AS RunningTotal
--                               FROM  cteReverseInSum AS z
--                               WHERE z.sku = w.sku
--                                     AND z.RollingStock >= w.TotalStock
--                               ORDER BY  z.created_at DESC
--                               LIMIT 1
--                             ) AS LastPartialStock
--      )
    AS ( select distinct 
        w.sku,
        w.TotalStock,
        last_value(created_at) over (partition by w.sku order by z.created_at rows between current row and unbounded following) as created_at
        from cteStockSum w
        join cteReverseInSum z
        on w.sku = z.sku
        and z.RollingStock >= w.TotalStock
    )

SELECT  
    y.sku,
    e.TotalStock,
    SUM(CASE 
        WHEN e.created_at = y.created_at 
        THEN e.TotalStock - (y.RollingStock - y.ThisStock)
    ELSE y.ThisStock
    END * Price.unit_cost) AS CurrentValue
    FROM cteReverseInSum AS y
    join cteWithLastTranDate e
    ON e.SKU = y.SKU
CROSS JOIN LATERAL ( 
/* Find the Price of the item in */ SELECT
                                    p.unit_cost
                            FROM the_schema.mockup_inv_movements AS p 
                            WHERE   p.SKU = e.SKU
                                    AND p.created_at <= e.created_at
                                    AND p.adjustment_quantity > 0
                            ORDER BY p.created_at DESC
                            LIMIT 1
            ) AS Price
WHERE            
       y.created_at >= e.created_at
GROUP BY y.sku ,e.TotalStock
ORDER BY y.sku

then result is the same <3

+-------------+--------------+--------------+
|     sku     | totalstock   | currentvalue |
+-------------+--------------+--------------+
| bar_product |           75 |       545.00 |
| foo_product |           25 |       155.00 |
+-------------+--------------+--------------+

before performance

compare after using window function performance

explain step by step:

It is used to calculate the current value of a stock based on its purchase history. The query uses multiple common table expressions (CTE) to calculate the current value of each stock item.

The first CTE named cteStockSum calculates the total stock of each item by summing up all the adjustment quantities for each SKU.

The second CTE named cteReverseInSum calculates the rolling stock of each item by summing up all the adjustment quantities for each SKU in reverse chronological order.

The third CTE named cteWithLastTranDate calculates the last transaction date for each SKU.

Finally, the main query joins all three CTEs and calculates the current value of each SKU based on its purchase history.

Please signup or login to give your own answer.

Click here to cancel reply.

How can I write a Postgres (SQL) query for FIFO 'closing stock' inventory valuation? – CentOS

Answers

2023 shorter version using postgres 15 and window function

explain step by step: