select
strftime('%m', TRANSDATE) as Month,
total (case when TRANSCODE='Deposit' then amount else 0 end) as Deposit,
total (case when TRANSCODE='Withdrawal' then amount else 0 end) as Withdrawal
from (select c.TRANSDATE,
c.TRANSCODE,
case when c.categid=-1 then s.splittransamount else c.transamount end as amount
from checkingaccount_v1 c
left join splittransactions_v1 s on s.transid = c.transid
where c.TRANSCODE <>'Transfer'
--and strftime('%m', TRANSDATE)='01' and strftime('%Y', TRANSDATE)='2014'
and TRANSDATE >= date('now','start of month','-2 month') and TRANSDATE <= date('now','start of month','+1 month','-1 day') ) t
group by strftime('%m', TRANSDATE)
On the income v expenses report it excludes transfers, however I have used MMEX to show my credit cards as well as my bank accounts and the transfers represent payments to the credit cards, is it possible to have the option of adding these?
I do similar to you with our credit cards. What I've found is this SQL works in this case. The transactions on the credit card are part of the expense column (refunds are in the income column). You can see it broken down with this SQL.
select
strftime('%m', TRANSDATE) as Month,
t.ACCOUNTID,
a.ACCOUNTNAME,
total (case when TRANSCODE='Deposit' then amount else 0 end) as Deposit,
total (case when TRANSCODE='Withdrawal' then amount else 0 end) as Withdrawal
from (
select
c.TRANSDATE,
c.TRANSCODE,
c.ACCOUNTID,
case when c.categid=-1 then s.splittransamount else c.transamount end as amount
from
checkingaccount_v1 c
left join splittransactions_v1 s on s.transid = c.transid
where
c.TRANSCODE <>'Transfer'
--and strftime('%m', TRANSDATE)='01' and strftime('%Y', TRANSDATE)='2014'
and TRANSDATE >= date('now','start of month','-2 month') and TRANSDATE <= date('now','start of month','+1 month','-1 day')
) t
left join accountlist_v1 a on a.accountid = t.accountid
group by
strftime('%m', TRANSDATE),
t.ACCOUNTID
;
However, am I missing something, everyone is talking about SQL but I don't seem to be able to change my reports is there some additional software I need?