guanlisheng wrote:Hi Dimitrios, thanks for pointing active months count issue and i just post a in-place fix on gist.
Regarding the SQL aspect for general report, it should be less than 50 lines which meant we can read all sql in on screen. and better to write code in gist and just post on link in forum.
guanlisheng hi!
I have managed to make the entire output recordset into a single SQL query. It is somewhat larger that 50 lines, 78 lines to be exact, but what bothers the most is how the database engine would handle a particular subquery; i.e. the one which gets all withdrawals for both normal and splitted transactions. This is actually the one you fine tuned in your version of report:
Code: Select all
(select ifnull(split.categid, tran.categid) as categid,
ifnull(split.subcategid, tran.subcategid) as subcategid,
tran.transdate as transdate,
ifnull(split.splittransamount, tran.transamount) as transamount,
tran.notes
from checkingaccount_v1 as tran
left join splittransactions_v1 as split
on tran.transid = split.transid
where tran.payeeid != 15
and tran.subcategid <> 66
and tran.transcode = 'Withdrawal'
order by categid,subcategid,transdate
) categ_subcateg_withdrawals
group by categ_subcateg_withdrawals.categid, categ_subcateg_withdrawals.subcategid
It looks to me that the 3rd occurrence might be redundant, but I haven't figured a way to remove it yet. At any rate, though, this is the idea for computing a correct average which also covers for the case in which you pay expenses not only in a monthly base, but also bi-montly, 4 times/year, twice a year etc.
If you find an easier to way to do this please let me know
Thanks!
Dimitrios
EDIT:
OK... I managed to eliminate the 3rd occurrence of the categ_subcateg_withdrawals subuquery, improving the performance and simplicity of the overall statement. Please check the attached SQL.