Closed vomikan closed 4 years ago
@aprobinda could you create this report?
@vomikan original SQL code could be modified in the following way. I have tested on my accounts and it seems to show the right overview:
select a.ACCOUNTNAME, ( select a.INITIALBAL + total(t.TRANSAMOUNT) from (select ACCOUNTID, STATUS, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT ,TRANSDATE from CHECKINGACCOUNT_V1 union all select TOACCOUNTID, STATUS, TOTRANSAMOUNT ,TRANSDATE from CHECKINGACCOUNT_V1 where TRANSCODE = 'Transfer' ) as t where t.ACCOUNTID = a.ACCOUNTID and t.TRANSDATE <= '2017-02-25' and t.STATUS <> 'V' ) as Balance from ACCOUNTLIST_V1 as a where a.STATUS = 'Open' group by a.ACCOUNTNAME order by a.ACCOUNTNAME asc;
My SQL script is (based on your but with more readable structure):
with b as (
select ACCOUNTID, STATUS
, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
,TRANSDATE
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, STATUS, TOTRANSAMOUNT ,TRANSDATE
from CHECKINGACCOUNT_V1
where TRANSCODE = 'Transfer'
)
select a.ACCOUNTNAME,
total(TRANSAMOUNT) + a.INITIALBAL as Balance
from ACCOUNTLIST_V1 as a, b
where a.STATUS = 'Open'
and b.ACCOUNTID = a.ACCOUNTID
and b.STATUS <> 'V'
and b.TRANSDATE <= '2017-02-25'
group by a.ACCOUNTNAME order by a.ACCOUNTNAME asc;
There is already issued the report 'Account Summary'
I'll modified it regarding this issue.
Seems it would be nice option to add a date option.
Only for mmex >= 1.4.0 Account summary.zip
Use nightly biild for tests
@petubl Possible SQL for new DB structure:
with t as
(select ACCOUNTID,
STATUS,
TRANSDATE,
(case
when TRANSCODE = 'Deposit' then
TRANSAMOUNT
else
-TRANSAMOUNT
end) as TRANSAMOUNT
from CHECKINGACCOUNT
where STATUS NOT LIKE 'V%'
union all
select TOACCOUNTID, STATUS, TRANSDATE, TOTRANSAMOUNT
from CHECKINGACCOUNT
where TRANSCODE = 'Transfer'
and STATUS NOT LIKE '_V'),
c as
(with h as
(select CURRENCYID, max(CURRVALUE) CURRVALUE
from CURRENCYHISTORY
where CURRDATE <= '&single_date'
group by CURRENCYID)
select c.CURRENCYID,
c.PFX_SYMBOL,
c.SFX_SYMBOL,
ifnull(h.CURRVALUE, 1) BASECONVRATE
from CURRENCYFORMATS c
left join h
on h.CURRENCYID = c.CURRENCYID)
select a.ACCOUNTNAME,
c.PFX_SYMBOL,
c.SFX_SYMBOL,
c.BASECONVRATE,
total(t.TRANSAMOUNT) + a.INITIALBAL as Balance
from ACCOUNTLIST as a, t, c
where a.ACCOUNTTYPE not in ('Investment')
and c.CURRENCYID = a.CURRENCYID
and a.STATUS = 'Open'
and a.ACCOUNTID = t.ACCOUNTID
and t.TRANSDATE <= '&single_date'
group by a.ACCOUNTNAME
order by a.ACCOUNTNAME asc;
Thank you very much @vomikan the sql code works now - i still get many decimal points on the 1st, 3rd, 5th, 7th etc lines on the report.
Any idea why this happens?
EDIT
I had only checked the sql test output.
The actual report renders beautifully! Thank you @vomikan
@petubl I've just updated SQL code, please, update
Most important changes:
Works great! Thanks
I've created wet (draft) report with other graph generator. Account.summary_10.zip
It more complicated but looks better. But needed a lot of fixes.
The new graph looks very good! I prefer the multicolored list from the original report.
Thanks for your time in doing this!
@vomikan I spent some time playing around with the new report and have the following to report:
In the chart, the largest four accounts are represented by a different colour (orange, green, blue, purple). However, the remaining accounts are displayed in the chart using the same colour as the one used in the case of the fourth one. So accounts 4, 5, 6, 7 etc are the same colour, purple - or maybe they are different shades of purple (those accounts have much lower balances and their share of the pie is quite small so i am not sure if there is a shade difference).
In the table of the previous version of the report, you used a different colour for each total on the column on the right, labelled "Base Currency". I think it made the report more pleasing to the eye, but is not included in the new version.
It would be great if the colours were brought back to the last column of the report, and then the same colours were used in the chart - I don't know if its something that is easily doable, and which you might be inclined to do...
Thank you once again for your time in looking into this.
http://forum.moneymanagerex.org/viewtopic.php?f=23&t=6952