Page 1 of 1

Budget Report

Posted: Fri Sep 01, 2017 7:03 am
by jaywhy
I have a sql query which returns a list of categories and their budget and actual amounts.

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
Is there a way with Lua or something else to split the results into two tables on the output - one for expenses and one for income based on category name starts with x? I am new to MMEX and have never come accross Lua before so please excuse my ignorance!

Re: Budget Report

Posted: Mon Sep 04, 2017 9:54 am
by jaywhy
Further to the above, I have found an html template which means I could potentially manipulate the results of a sql query using javascript (which is perfect for me) from this post: viewtopic.php?f=16&t=5521&p=10890&hilit=json#p10890. However, the tmpl_var JSONCONTENTS is not returning the results of my sql query. Has the variable name changed or am I misunderstanding this?

Please someone help me...!

Re: Budget Report

Posted: Tue Sep 05, 2017 6:19 am
by jaywhy
I am using MMEX version 1.3.3 on Windows 10 if that helps. I struggle to believe that nobody knows the answer to this question!!

Re: Budget Report

Posted: Thu Sep 07, 2017 6:34 pm
by Nikolay
I can help.