mysql - show the beginning balance and ending balance based on date selected PHP mysqlquery

Question

I changed my transaction to one single table. How can I show the beginning balance in and ending balance based on the date selected.

Here's my new Transaction table.

TBL NAME: transaction_history

trans_itemdesc	trans_qtyin	trans_wtin	trans_datein	trans_qtyout	trans_wtout	trans_dateout	status
Item1	500	5000	2020-11-01
Item2	300	3000	2020-11-25
Item1	200	2000	2020-11-01
Item1				50	2020-11-11
Item2				25	2020-11-31		OPEN
Item1				40	2020-11-31		OPEN
Item1				100	2020-11-04		OPEN

question from:https://stackoverflow.com/questions/65910682/show-the-beginning-balance-and-ending-balance-based-on-date-selected-php-mysqlqu

Answer 1

You really should just have one table; separate in and out tables make more problems than they solve. Guessing at some of your column names, I suspect you don't really have a column named 'Date Out' etc

select product as Product,
    coalesce(sum(case when dt < '2020-12-01' then amount end),0) as BegBal,
    coalesce(sum(case when dt between '2020-12-01' and '2020-12-31' and type='in' then amount end),0) as In,
    coalesce(sum(case when dt between '2020-12-01' and '2020-12-31' and type='out' then -amount end),0) as Out,
    coalesce(sum(case when dt <= '2020-12-31' then amount end),0) as EndBal
from (
    select 'in' as type, product, date_in as dt, in as amount from in
    union all
    select 'out' as type, product, date_out, -amount from out
) in_out;