Page 1 of 1
Reports
Posted: Sat Mar 07, 2015 4:38 pm
by alaintz
Hi, I am quite new with MMEX (on MacAir Pro) which is already matching 99% of my needs. I miss a specific report and I am not familiar enough with SQL to create mine; if somebody could assist me ???? I need for a period of time ( let's say one specific year or one specific month or ....) the accounts balance history ( let's say each end of month when asking year history, each day when asking one specific month etc.. ) The data I need is the sum of all bank account balances ( current, CC ..). Possible to make it account specific ( like revenues/expenses existing report) Thanks for help !!
Re: Reports
Posted: Sat Mar 07, 2015 9:40 pm
by Nikolay
It may be combination of this
Code: Select all
select t.id
, a.INITIALBAL + total(t.TRANSAMOUNT) as Balance
from
(select ACCOUNTID
, case when (julianday(TRANSDATE)- julianday('2015-01-01'))<0 then 0 else TRANSID end as ID
, STATUS,
(case when TRANSCODE='Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID
, case when (julianday(TRANSDATE)- julianday('2015-01-01'))<0 then 0 else TRANSID end
, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE='Transfer') as t
inner join ACCOUNTLIST_V1 as a on a.ACCOUNTID=t.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c on a.CURRENCYID=c.CURRENCYID
where a.Status='Open'
and t.STATUS<>'V'
and a.accountname = 'BBP:1'
group by t.id
And
http://forum.moneymanagerex.org/viewtop ... =16&t=5909