Code: Select all
select c.transid, c.transdate
,a.accountname
, case c.transcode when 'Transfer' then t.accountname else p.payeename end payee
, ca.categname || case c.subcategid when -1 then '' else ':'||S.SUBCategName end as Category
, case c.transcode when 'Deposit' then c.transamount else -c.transamount end as Amount
, c.notes
, cur.currency_symbol
from checkingaccount_v1 c
left join payee_v1 p on p.payeeid = c.payeeid
left join category_v1 ca on c.categid = ca.categid
left join SUBCATEGORY_V1 S on S.SUBCATEGID = C.SUBCATEGID and C.CATEGID = S.CATEGID
left join ACCOUNTLIST_V1 a on a.ACCOUNTID = C.ACCOUNTID
left join ACCOUNTLIST_V1 t on t.ACCOUNTID = C.toACCOUNTID
left join CURRENCYFORMATS_V1 cur on cur.currencyid = a.currencyid
where c.status = 'F'
order by c.transdate