Income vs Expences

Page 1 of 1
7 posts
Income vs Expences

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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

MarcusAurelios
New MMEX User

Posts: 3
Joined: Mon Mar 24, 2014 9:12 am
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

thompsonson
New MMEX User

Posts: 13
Joined: Sat Sep 01, 2012 3:32 am
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

MarcusAurelios
New MMEX User

Posts: 3
Joined: Mon Mar 24, 2014 9:12 am
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

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Re: Income vs Expences

MarcusAurelios
New MMEX User

Posts: 3
Joined: Mon Mar 24, 2014 9:12 am
[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

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 3 guests