Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE 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
);

CREATE TABLE


-- insert receipt / 'in' records
INSERT INTO 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 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 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 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')
;

INSERT 0 3



INSERT 0 4



INSERT 0 4



INSERT 0 4


select 
id
, created_at
, sku
, adjustment_quantity
, unit_cost
, po_num
, sum(adjustment_quantity) over(partition by sku order by created_at) qty_on_hand
from mockup_inv_movements


id
created_at
sku
adjustment_quantity
unit_cost
po_num
qty_on_hand


4
2024-04-26 12:55:13.430244+01
bar_product
100
5.00
123
100

5
2024-04-26 13:55:13.430244+01
bar_product
30
6.00
963
130

6
2024-04-26 14:55:13.430244+01
bar_product
50
8.00
223
180

7
2024-04-26 15:55:13.430244+01
bar_product
5
5.00
456
185

8
2024-04-26 16:55:13.430244+01
bar_product
-50
null
null
135

9
2024-04-26 17:55:13.430244+01
bar_product
-30
null
null
105

10
2024-04-26 18:55:13.430244+01
bar_product
-20
null
null
85

11
2024-04-26 19:55:13.430244+01
bar_product
-10
null
null
75

1
2024-04-26 09:55:13.430244+01
foo_product
100
4.00
123
100

2
2024-04-26 10:55:13.430244+01
foo_product
10
3.00
987
110

3
2024-04-26 11:55:13.430244+01
foo_product
20
7.00
223
130

12
2024-04-26 20:55:13.430244+01
foo_product
-70
null
null
60

13
2024-04-26 21:55:13.430244+01
foo_product
-5
null
null
55

14
2024-04-26 22:55:13.430244+01
foo_product
-20
null
null
35

15
2024-04-26 23:55:13.430244+01
foo_product
-10
null
null
25



SELECT 15


/* 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
     )
select * from cteStockSum


sku
totalstock


bar_product
75

foo_product
25



SELECT 2


/* Sum up the ins and outs to calculate the remaining stock level */
WITH 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
     )
select * from cteReverseInSum


sku
created_at
rollingstock
thisstock


foo_product
2024-04-26 09:55:13.430244+01
130
100

foo_product
2024-04-26 10:55:13.430244+01
30
10

foo_product
2024-04-26 11:55:13.430244+01
20
20

bar_product
2024-04-26 12:55:13.430244+01
185
100

bar_product
2024-04-26 13:55:13.430244+01
85
30

bar_product
2024-04-26 14:55:13.430244+01
55
50

bar_product
2024-04-26 15:55:13.430244+01
5
5



SELECT 7


/* 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
     )
select * from cteWithLastTranDate


sku
totalstock
created_at
stocktouse
runningtotal
usethisstock


bar_product
75
2024-04-26 13:55:13.430244+01
30
85
20

foo_product
25
2024-04-26 10:55:13.430244+01
10
30
5



SELECT 2


/* 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


sku
currentitems
currentvalue


bar_product
75
545.00

foo_product
25
155.00



SELECT 2

id	created_at	sku	adjustment_quantity	unit_cost	po_num	qty_on_hand
4	2024-04-26 12:55:13.430244+01	bar_product	100	5.00	123	100
5	2024-04-26 13:55:13.430244+01	bar_product	30	6.00	963	130
6	2024-04-26 14:55:13.430244+01	bar_product	50	8.00	223	180
7	2024-04-26 15:55:13.430244+01	bar_product	5	5.00	456	185
8	2024-04-26 16:55:13.430244+01	bar_product	-50	null	null	135
9	2024-04-26 17:55:13.430244+01	bar_product	-30	null	null	105
10	2024-04-26 18:55:13.430244+01	bar_product	-20	null	null	85
11	2024-04-26 19:55:13.430244+01	bar_product	-10	null	null	75
1	2024-04-26 09:55:13.430244+01	foo_product	100	4.00	123	100
2	2024-04-26 10:55:13.430244+01	foo_product	10	3.00	987	110
3	2024-04-26 11:55:13.430244+01	foo_product	20	7.00	223	130
12	2024-04-26 20:55:13.430244+01	foo_product	-70	null	null	60
13	2024-04-26 21:55:13.430244+01	foo_product	-5	null	null	55
14	2024-04-26 22:55:13.430244+01	foo_product	-20	null	null	35
15	2024-04-26 23:55:13.430244+01	foo_product	-10	null	null	25