Shows Income Expense from last year with graph

Page 1 of 1
4 posts
Shows Income Expense from last year with graph

sterling
Super MMEX User

Posts: 82
Joined: Thu Dec 11, 2014 2:40 am
Hi,

This report shows the income expense from beginning of last year, up to beginning of this month.  It also plots the income/expense on a graph with the months labelled.  This is the one i was having trouble with getting the label data through lua, but i've ended up not using it at all.  The JavaScript just access the columns directly.
Attachments
Income Expense Year.grm
(1.88 KiB) Downloaded 338 times
Re: Shows Income Expense from last year with graph

nuvito
New MMEX User

Posts: 17
Joined: Mon Jun 11, 2012 11:15 am
Hi,

cool report.

If I may give you a little SQL hint to shorten the query: You can avoid the self join by using conditional sums with the group-by:

Code: Select all
select transdate,dep.date, Income,Outcome,Income-Outcome as Profit
from
(
    select
        strftime('%Y', CHECKINGACCOUNT_V1.TRANSDATE) as YEAR,
        strftime('%m', CHECKINGACCOUNT_V1.TRANSDATE) as MONTH,
        strftime('%m-%Y', CHECKINGACCOUNT_V1.TRANSDATE) as DATE,
        CHECKINGACCOUNT_V1.TRANSDATE,
        sum(case when Transcode = 'Deposit' then TRANSAMOUNT else 0 end) as Income,
        sum(case when Transcode = 'Withdrawal' then TRANSAMOUNT else 0 end) as Outcome
    from
        CHECKINGACCOUNT_V1
        join
            ACCOUNTLIST_V1 on CHECKINGACCOUNT_V1.ACCOUNTID=ACCOUNTLIST_V1.ACCOUNTID
        join
            PAYEE_V1 on PAYEE_V1.PAYEEID=CHECKINGACCOUNT_V1.PAYEEID
    where TransCode in ('Deposit', 'Withdrawal')
    group by year ,month
) as dep
WHERE (dep.transdate >= date('now', 'start of YEAR', '-1 year') and dep.transdate < date('now', 'start of month'))

order by year,month;
As you can see, the query just sums the transamount as income, if the transcode equals 'Deposit', and as outcome, if the transcode equals 'Withdrawal'. So, no self-join is necessary any more.

The two relevant values for TransCode are selected by the where clause. This is useful, as the where clause will be evaluated before the grouping.

Perhaps you ccould need that for further reports.
Kind regards,

nuvito
Re: Shows Income Expense from last year with graph

sterling
Super MMEX User

Posts: 82
Joined: Thu Dec 11, 2014 2:40 am
Thank you so much for your feedback.  All is welcome.  I'm a programmer by trade but not web or SQL, so this is a bit of a hobby.

I've got another report to post in a bit and id be very interested in feedback on that because I feel it could be optimised even more than this one.
Re: Shows Income Expense from last year with graph

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
Maybe we can post it in Github?
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 7 guests

cron