Income vs Expences

[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)

[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

[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
;

[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?

[Nikolay]

https://sourceforge.net/projects/moneym ... _unstable/

https://sourceforge.net/p/moneymanagere ... 20Manager/

[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.

[Nikolay]

it's working only with beta 1.0.1.0
https://sourceforge.net/projects/moneym ... _unstable/