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 |