Page 1 of 1
Shows Income Expense from last year with graph
Posted: Sun Jan 04, 2015 10:40 am
by sterling
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.
Re: Shows Income Expense from last year with graph
Posted: Sun Jan 04, 2015 8:03 pm
by nuvito
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.
Re: Shows Income Expense from last year with graph
Posted: Sun Jan 04, 2015 8:56 pm
by sterling
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
Posted: Tue Jan 06, 2015 12:55 pm
by guanlisheng
Maybe we can post it in Github?