Code: Select all
select
ifnull(CategoryName,(select categname from CATEGORY_V1 where ifnull(CategoryName,rtrim(btj,-1)) = categid)) Category
,abs(ifnull(Budget,0)) Budget
,case when CategoryName like 'Income%' then abs(ifnull(Actual,0)) else ifnull(Actual,0) end Actual
,abs(ifnull(Budget,0))-case when CategoryName like 'Income%' then abs(ifnull(Actual,0)) else ifnull(Actual,0) end Difference
from
(select
c.catjoin ccj
,bt.catjoin btj
,CategoryName
,Budget
,Actual
from
(select
ca.categid||ifnull(s.subcategid,'-1') catjoin
,case ifnull(subcategname,'') when '' then categname else categname||' : '||subcategname end CategoryName
from CATEGORY_V1 ca
left join SUBCATEGORY_V1 s on ca.categid=s.categid) c
left join
(select
ifnull(bj,tj) catjoin
,Budget
,Actual
from
(select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
(select
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
left join
(select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then splittransamount else transamount end) else case che.categid when -1 then splittransamount else transamount end end) Actual
from checkingaccount_v1 che
left join splittransactions_v1 s on s.transid=che.transid
group by categ||subcateg) t
using (joiner)
union all
select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
(select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then splittransamount else transamount end) else case che.categid when -1 then splittransamount else transamount end end) Actual
from checkingaccount_v1 che
left join splittransactions_v1 s on s.transid=che.transid
group by categ||subcateg) t
left join
(select
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
using (joiner)
where b.joiner is null)) bt
using (catjoin)
union all
select
c.catjoin ccj
,bt.catjoin btj
,CategoryName
,Budget
,Actual
from
(select
ifnull(bj,tj) catjoin
,Budget
,Actual
from
(select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
(select
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
left join
(select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then splittransamount else transamount end) else case che.categid when -1 then splittransamount else transamount end end) Actual
from checkingaccount_v1 che
left join splittransactions_v1 s on s.transid=che.transid
group by categ||subcateg) t
using (joiner)
union all
select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
(select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then splittransamount else transamount end) else case che.categid when -1 then splittransamount else transamount end end) Actual
from checkingaccount_v1 che
left join splittransactions_v1 s on s.transid=che.transid
group by categ||subcateg) t
left join
(select
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
using (joiner)
where b.joiner is null)) bt
left join
(select
ca.categid||ifnull(s.subcategid,'-1') catjoin
,case ifnull(subcategname,'') when '' then categname else categname||' : '||subcategname end CategoryName
from CATEGORY_V1 ca
left join SUBCATEGORY_V1 s on ca.categid=s.categid) c
using (catjoin)
where c.catjoin is null)
where Category != 'Transfer'
and Category != 'Budget S/D : Z'
order by Category