[code]
select a.ACCOUNTNAME, c.BASECONVRATE, c.PFX_SYMBOL, c.SFX_SYMBOL,
(select INITIALBAL + total(TRANSAMOUNT)
from
(select ACCOUNTID, STATUS,
(case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE = 'Transfer') as t
where t.ACCOUNTID = a.ACCOUNTID
and t.STATUS <> 'V') as Balance
from ACCOUNTLIST_V1 as a
inner join CURRENCYFORMATS_V1 as c on c.CURRENCYID = a.CURRENCYID
where a.ACCOUNTTYPE in ('Checking', 'Term') and a.STATUS = 'Open'
group by a.ACCOUNTNAME
order by a.ACCOUNTNAME asc;
[/code]
Lua
[code]
local total = 0;
function handle_record(record)
local bal = string.format("%.2f", record:get('Balance'));
local base = bal * record:get("BASECONVRATE");
record:set("Balance", record:get('PFX_SYMBOL') .. bal .. record:get('SFX_SYMBOL'));
record:set("Base", base);
total = total + base;
end
function complete(result)
result:set("Total", total);
end
[/code]