Account Statistics

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)
Post Reply
Nikolay
MMEX Developer
Posts: 1364
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Account Statistics

Post by Nikolay »

Code: Select all

select 
total(case strftime('%m', TRANSDATE) when '01' then transamount * c.BASECONVRATE end) as Jan,
total(case strftime('%m', TRANSDATE) when '02' then transamount * c.BASECONVRATE end) as Feb,
total(case strftime('%m', TRANSDATE) when '03' then transamount * c.BASECONVRATE end) as Mar
from
(select accountid, TRANSDATE, STATUS,
(case when TRANSCODE='Deposit' then transamount else -transamount end) as transamount
from checkingaccount_v1 c 
union all
select
toaccountid, TRANSDATE, STATUS, totransamount
from checkingaccount_v1 c 
where transcode='Transfer') t
inner join accountlist_v1 a on a.accountid=t.accountid
inner join currencyformats_v1 c on a.currencyid=c.currencyid
where a.accountname in ('1','2')
and t.STATUS <>'V'
and strftime('%Y', TRANSDATE)='2014'
group by strftime('%m', TRANSDATE), a.accountid;

Post Reply