Page 1 of 1
Income vs Expences
Posted: Wed Feb 12, 2014 9:07 pm
by Nikolay
Code: Select all
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)
Re: Income vs Expences
Posted: Mon Mar 24, 2014 3:20 pm
by MarcusAurelios
Hi Nikolay,
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?
Regards
Re: Income vs Expences
Posted: Sun Apr 06, 2014 9:02 am
by thompsonson
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.
Code: Select all
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
;
Re: Income vs Expences
Posted: Tue Apr 08, 2014 10:07 am
by MarcusAurelios
Thanks for your help thompsonson.
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?
Re: Income vs Expences
Posted: Tue Apr 08, 2014 11:10 am
by Nikolay
Re: Income vs Expences
Posted: Tue Apr 08, 2014 3:13 pm
by MarcusAurelios
[img]
http://i57.tinypic.com/2z4hi4m.jpg[/img] (Sorry not sure why this isn't working)
This is not showing on the version I downloaded (v 1.0.0.3)
Edit:
Ah I have now seen that grm is disabled in the newer versions so have downloaded an older version to see how I get on with creating my own reports.
Thanks for all assistance.
Re: Income vs Expences
Posted: Tue Apr 08, 2014 5:07 pm
by Nikolay