PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Calculate sales depending on stock entries

I’m working on a BI request and I’m stuck and need your help 🙂

Here’s the context :
My company is receiving products and selling them. Until here, nothing complex 🙂
The selling price is varying depending on my available stock and future incoming products. The operation to fix the selling price is manual. We register the historic of the price in a table.

I would like to know for each income, the total selling price. Some incomes can overlaps each other.

The stock rule is FIFO. So we first complete the first income with the firsts sold quantities the the oder one and so on. At the time of the request, it’s possible that we still have stock in our warehouse.

Here’s an example :

. Date Stock move quantity Sell price
01/01/24 +120
01/02/24 -20 100
01/03/24 -40 80
01/04/24 +100
01/05/24 -50 100
01/06/24 -40 90
01/07/24 +60
01/08/24 -20 100
01/09/24 -40 90
01/10/24 -40 80

So the result should be :

Income 1 : 10300  -> 20 *100 + 40 *80 + 50 *100 + 10 (to complete 120 qty) *90 = 10300
Income 2 : 9100   -> 30 (the rest of sale on 01/06/24 : 40-10) *90 + 20 *100 + 40 *90 + 10 (to complete 100 qty) *80 = 9100
Income 3 : 2400   -> 30 (the rest of sale on 01/10/24 : 40-10) *80 = 2400.

There is still 30 in stock but don’t need to appear in the result.

I’m working in POSTGRE SQL

Tags:

2

Answers


  1. 0

    First CTE calculate rolling balance for every income or outcome stock move.
    Accordingly bal_in is incoming (balance before operation) and and bal_out is outcoming. We calculate rollup sum separately for incomes and outcomes.
    Our task is to JOIN the movements – in and out – with intersection of (bal_in,bal_out) for incoming and outcoming’s.
    For example:
    for incoming 100 bal_in=0, bal_out=120, inc103 bal_in=120 bal_out=220.
    for outcoming 105 bal_in=110, bal_out=150
    Intersection (JOIN) is from 110 to 120 with inc100
    and 120-150 with inc103

    See example.
    I added 1 row of data to your example, and also introduced additional columns(identifiers) for the product and its movement. It’s clearer and clearer this way.

    move_id product_id date stock_move_quantity sell_price
    100 1 2024-01-01 120 null
    101 1 2024-02-01 -20 100
    102 1 2024-03-01 -40 80
    103 1 2024-04-01 100 null
    104 1 2024-05-01 -50 100
    105 1 2024-06-01 -40 90
    106 1 2024-07-01 60 null
    107 1 2024-08-01 -20 100
    108 1 2024-09-01 -40 90
    109 1 2024-10-01 -40 80
    110 1 2024-11-01 50 null 
    with rollups as(
  select product_id,move_id,date,abs(Stock_move_quantity) qty,sell_price
     ,bal_out-abs(Stock_move_quantity) bal_in, bal_out
     ,case when Stock_move_quantity>=0 then 'I' else 'O' end fl
     ,product_rest
  from(
  select * 
    ,abs(sum(Stock_move_quantity) 
       over(partition by product_id,(Stock_move_quantity>0) order by date,move_id
            )) bal_out
    ,sum(Stock_move_quantity) 
       over(partition by product_id order by date,move_id
            ) product_rest
  from test
 )a
)
,in_out_align as(
select i.product_id,i.move_id inc_id,i.date inc_date
  ,o.move_id out_id,o.date out_date,i.qty inc_qty,o.qty out_qty
  ,least(i.bal_out,o.bal_out)-greatest(i.bal_in,o.bal_in) move_qty
  ,o.sell_price 
  ,greatest(i.bal_in,o.bal_in) ifrom
  ,least(i.bal_out,o.bal_out) ito
  ,i.bal_out tot_inc,o.bal_out tot_out
  ,coalesce(o.product_rest,i.product_rest) product_rest
from rollups i
left join rollups o on i.product_id=o.product_id 
  and i.bal_in<o.bal_out and o.bal_in<i.bal_out
  and (o.fl='O' or o.fl is null)
where i.fl='I' 
)
select product_id,inc_id,inc_date,inc_qty
  ,out_id,out_date,out_qty
  ,move_qty,sell_price
  ,sum(move_qty*sell_price)over(partition by product_id,inc_id) tot_sell
  ,ito-ifrom party_rest
  ,product_rest
from in_out_align
order by out_date,out_id,inc_id
    product_id inc_id inc_date inc_qty out_id out_date out_qty move_qty sell_price tot_sell party_rest product_rest
    1 100 2024-01-01 120 101 2024-02-01 20 20 100 11100 20 100
    1 100 2024-01-01 120 102 2024-03-01 40 40 80 11100 40 60
    1 100 2024-01-01 120 104 2024-05-01 50 50 100 11100 50 110
    1 100 2024-01-01 120 105 2024-06-01 40 10 90 11100 10 70
    1 103 2024-04-01 100 105 2024-06-01 40 30 90 9100 30 70
    1 103 2024-04-01 100 107 2024-08-01 20 20 100 9100 20 110
    1 103 2024-04-01 100 108 2024-09-01 40 40 90 9100 40 70
    1 103 2024-04-01 100 109 2024-10-01 40 10 80 9100 10 30
    1 106 2024-07-01 60 109 2024-10-01 40 30 80 2400 30 30
    1 110 2024-11-01 50 null null null 50 null null 50 80

    fiddle

    Login or Signup to reply.
  2. 0

    An interesting problem! I will use the word "sale" for negative amounts, and for "income" I use "supply" (positive amounts).

    One possible query plan is to number each stock item on the order it is supplied, and then draw items for sale in the order of stock number. That is, the first supply here generates items 1-120, the second supply generates items 121-220. On the sales side, the first sale will consume items 1-20, and second items 21-60 and so on.

    We can then use these stock numbers like a time line to relate sales with supplies, that is combine sales with supplies by overlaping stock numbers.

    Here is a db<>fiddle. I will explain the steps below.

    The first step provides the input data which you provided with the question:

    with
data(ts, amount, price) as (
  values ('01/01/24', 120, null),
         ('01/02/24', -20,  100),
         ('01/03/24', -40,   80),
         ('01/04/24', 100, null),
         ('01/05/24', -50,  100),
         ('01/06/24', -40,   90),
         ('01/07/24',  60, null),
         ('01/08/24', -20,  100),
         ('01/09/24', -40,   90),
         ('01/10/24', -40,   80)
),

    Next, we sum the amounts, ordered by timestamps, to provide the end-points of stock item number intervals. We do this separately for sales and supplies, and we sum the absolute value for sales, of course:

    sums as (
  select ts, amount, sum(abs(amount)) over (partition by sign(amount) order by ts), price
  from    data
),

    Next, we build the intervals from the interval endpoints, by the calculating the start-points. We no longer need the amount, but we need to know if it was a sale or supply:

    intervals as (
  select ts, amount < 0 sale, int8range(sum - abs(amount), sum) r, price
  from sums
),

    We want to join the supplies with sales on overlapping stock item number intervals. For this we need to split the transactions into supplies and sales:

    sale as (
  select ts, r, price
  from intervals
  where sale
),
supply as (
  select ts, r
  from intervals
  where not sale
),

    The next step is to join them. We use the range overlap (&&) and intersection (*) operators:

    partial_sales as (
  select supply.ts supply, sale.ts sale, upper(supply.r * sale.r)-lower(supply.r * sale.r) amount, price
  from   supply, sale
  where  supply.r && sale.r

    The result at this stage is
    | supply | sale | amount | price |
    |:——-|:—–|——-:|——:|
    | 01/01/24 | 01/02/24 | 20 | 100 |
    | 01/01/24 | 01/03/24 | 40 | 80 |
    | 01/01/24 | 01/05/24 | 50 | 100 |
    | 01/01/24 | 01/06/24 | 10 | 90 |
    | 01/04/24 | 01/06/24 | 30 | 90 |
    | 01/04/24 | 01/08/24 | 20 | 100 |
    | 01/04/24 | 01/09/24 | 40 | 90 |
    | 01/04/24 | 01/10/24 | 10 | 80 |
    | 01/07/24 | 01/10/24 | 30 | 80 |

    It is easy to see that the supply from 01/01/24 combines with four sales, and the sales at 01/06/24 matches two supplies, each contributing some to the sale.

    All that remains here is to group by the supply date, and calculate the average profit per supply:

    select supply, sum(amount*price)/sum(amount) "avg sales price"
from partial_sales
group by supply
order by supply

    The result is:
    | supply | avg sales price |
    |:——-|—————-:|
    | 01/01/24 | 92.5000000000000000 |
    | 01/04/24 | 91.0000000000000000 |
    | 01/07/24 | 80.0000000000000000 |

    There are other approaches to this, like continuing accumulation, e.g. ValNiks solution above. It is convenient to use ranges, but it also works without. The best approach depends on circumstances and performance or other non-functional requirements.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search