MySQL 8.0

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

CREATE TABLE t(
   m_date  DATE  NOT NULL
  ,item_id VARCHAR(7) NOT NULL
  ,qty     INTEGER  NOT NULL
);

INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-02','B-0001',4);
INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-03','B-0001',2);
INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-03','B-0001',-1);
INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-04','B-0001',-2);
INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-03','B-0002',5);
INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-03','B-0002',-2);
INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-04','B-0002',1);
INSERT INTO t(m_date,item_id,qty) VALUES ('2024-01-06','B-0002',-2);
select item_id, entry_date, -- with no date filtering
  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


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


select item_id, entry_date, -- with date filtering
  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
where entry_date between '2024-01-04' and '2024-01-31'
order by item_id, entry_date


item_id
entry_date
b_balance
mutation_in
mutation_out
e_balance


B-0001
2024-01-04
5
0
-2
3

B-0002
2024-01-04
3
1
0
4

B-0002
2024-01-06
4
0
-2
2

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