MMEX Discussion Forum

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;