select  2  as CURNUMBER,  ACCOUNTID, CURRENCYNAME, BALANCE, BASECONVRATE from (
	select t.accountid as ACCOUNTID, c.currencyname as CURRENCYNAME, 
        total (t.BALANCE) as BALANCE, 
        c.BASECONVRATE as BASECONVRATE 
        from ( 
        select  acc.accountid as ACCOUNTID, acc.INITIALBAL as BALANCE 
        from ACCOUNTLIST_V1 ACC 
        where ACC.STATUS='Open' 
        group by acc.accountid  
        union all 
        select  
        st.heldat as ACCOUNTID, 
        total((st.CURRENTPRICE-st.PURCHASEPRICE)*st.NUMSHARES-st.COMMISSION) as BALANCE 
        from  stock_v1 st 
	where st.purchasedate<=date ('now','localtime') 
        group by st.heldat 	
        union all 
        select ca.toaccountid,  total(ca.totransamount) 
        from checkingaccount_v1 ca 
        where ca.transcode ='Transfer' and ca.STATUS<>'V' and ca.transdate<=date ('now','localtime') 
        group by ca.toaccountid 
        union all 
        select ca.accountid,  total(case ca.transcode when 'Deposit' then ca.transamount else -ca.transamount end)  
        from checkingaccount_v1 ca 
        where ca.STATUS<>'V' and ca.transdate<=date ('now','localtime') 
        group by ca.accountid) t 
        left join accountlist_v1 a on a.accountid=t.accountid 
        left join  currencyformats_v1 c on c.currencyid=a.currencyid 
        where a.status='Open' and balance<> 0  
        group by c.currencyid)
        order by CURRENCYNAME