Stock Mutation with Mysql

Question

I have mutation table that stores transactions:

M_Date	Item_ID	Qty
2024-01-02	B-0001	4
2024-01-03	B-0001	2
2024-01-03	B-0001	-1
2024-01-04	B-0001	-2
2024-01-03	B-0002	5
2024-01-03	B-0002	-2
2024-01-04	B-0002	1
2024-01-06	B-0002	-2

the scenario is; I select begin date and end date (for example I choose from 2024-01-01 to 2024-01-31) to show data from mutation table like this :

Item_ID	entry_date	b_balance	Mutation_In	Mutation_Out	e_balance
B-0001	2024-01-02	0	4	0	4
B-0001	2024-01-03	4	2	-1	5
B-0001	2024-01-04	5	0	-2	3
B-0002	2024-01-03	0	5	-2	3
B-0002	2024-01-04	3	1	0	4
B-0002	2024-01-06	4	0	-2	2

how to query in mysql to get the above result? any help will be appreciated, thank you in advance!

Answer 1

You can do:

select item_id, entry_date,
  b_balance, 
  pos as mutation_in,
  neg as mutation_out,
  b_balance + pos + neg as e_balance
from (
  select x.*,
    coalesce(sum(neg + pos) over (partition by item_id order by entry_date 
      rows between unbounded preceding and 1 preceding), 0) as b_balance  
  from (
    select item_id, m_date as entry_date,
      sum(case when qty < 0 then qty else 0 end) as neg,
      sum(case when qty > 0 then qty else 0 end) as pos
    from t
    group by item_id, m_date
  ) x
) y
order by item_id, entry_date

Result:

 item_id  entry_date  b_balance  mutation_in  mutation_out  e_balance 
 -------- ----------- ---------- ------------ ------------- --------- 
 B-0001   2024-01-02  0          4            0             4         
 B-0001   2024-01-03  4          2            -1            5         
 B-0001   2024-01-04  5          0            -2            3         
 B-0002   2024-01-03  0          5            -2            3         
 B-0002   2024-01-04  3          1            0             4         
 B-0002   2024-01-06  4          0            -2            2         

See running example at db<>fiddle -- with/without filtering.

Answer 2

One option to do it could be like here:

--  S a m p l e    D a t a :
Create Table MUTATION (M_DATE DATE, ITEM_ID TEXT, QTY INT);
Insert Into MUTATION 
    VALUES  ('2024-01-02', 'B-0001', 4),
            ('2024-01-03', 'B-0001', 2),
            ('2024-01-03', 'B-0001', -1),
            ('2024-01-04', 'B-0001', -2),
            ('2024-01-03', 'B-0002', 5),
            ('2024-01-03', 'B-0002', -2),
            ('2024-01-04', 'B-0002', 1),
            ('2024-01-06', 'B-0002', -2);

--  S Q L :
SELECT m.ITEM_ID, m.M_DATE,
       Coalesce(Sum(m.MUTATION_QTY) 
                  Over(Partition By m.ITEM_ID Order By m.M_DATE
                    Rows Between Unbounded Preceding And 1 Preceding), 0) "B_BALANCE",
       m.MUTATION_IN, m.MUTATION_OUT, 
       Coalesce(Sum(m.MUTATION_QTY) 
                  Over(Partition By m.ITEM_ID Order By m.M_DATE
                    Rows Between Unbounded Preceding And 1 Preceding), 0) +
       m.MUTATION_QTY "E_BALANCE" 
FROM
    (Select ITEM_ID, M_DATE, 
            Sum(Case When QTY >= 0 Then QTY Else 0 END) "MUTATION_IN", 
            Sum(Case When QTY < 0 Then QTY Else 0 END)  "MUTATION_OUT",
            Sum(QTY) "MUTATION_QTY"
    From MUTATION
    Group By ITEM_ID, M_DATE) m;

/*      R e s u l t :
ITEM_ID  M_DATE       B_BALANCE  MUTATION_IN    MUTATION_OUT    E_BALANCE
-------  -----------  ---------  -----------  --------------  -----------
B-0001   2024-01-02           0            4               0            4
B-0001   2024-01-03           4            2              -1            5
B-0001   2024-01-04           5            0              -2            3
B-0002   2024-01-03           0            5              -2            3
B-0002   2024-01-04           3            1               0            4
B-0002   2024-01-06           4            0              -2            2    */