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!
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.