Budget Report

Page 1 of 1
4 posts
Budget Report

jaywhy
New MMEX User

Posts: 3
Joined: Fri Sep 01, 2017 12:49 am
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

jaywhy
New MMEX User

Posts: 3
Joined: Fri Sep 01, 2017 12:49 am
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

jaywhy
New MMEX User

Posts: 3
Joined: Fri Sep 01, 2017 12:49 am
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

Nikolay
MMEX Developer

Posts: 2262
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
I can help.
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest