Code: Select all
with c AS (
select c.CATEGID, -1 SUBCATEGID, c.CATEGNAME as CATEGORY
from CATEGORY_V1 c
union all
select c.CATEGID, s.SUBCATEGID
, c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
from CATEGORY_V1 c, SUBCATEGORY_V1 s
where c.CATEGID = s.CATEGID
order by CATEGORY
),
a AS (select a.ACCOUNTNAME, a.ACCOUNTID, c.CURRENCYID, c.CURRENCY_SYMBOL
from ACCOUNTLIST_V1 a, CURRENCYFORMATS_V1 c
where a.CURRENCYID=c.CURRENCYID
),
t AS (select t.ACCOUNTID, t.TRANSDATE , t.transcode, t.PAYEEID
, case ifnull(t.categid, -1) when -1 then s.categid else t.categid end categid
, case when ifnull(t.categid, -1) = -1 AND ifnull(t.subcategid, -1) = -1 then s.subcategid else t.subcategid end subcategid
, (case ifnull(s.splittransid, -1) when -1 then t.transamount else s.splittransamount end)
*(case t.transcode when 'Deposit' then 1 else -1 end) as Amount
from checkingaccount_v1 t
LEFT JOIN splittransactions_v1 s ON s.transid=t.transid
)
select c.CATEGORY Category
, total(t.AMOUNT) Amount
, '' Total
, t.CATEGID
from t
inner join a on a.ACCOUNTID=t.ACCOUNTID
left join c on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID
where t.TRANSDATE >=date('now','start of month')
and t.TRANSDATE <date('now','start of month','+1 month')
and a.CURRENCY_SYMBOL in ('RUB')
and c.CATEGORY not in ('Transfer', 'Loans')
group by t.CATEGID, t.SUBCATEGID
order by Category