Categories total current month

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Categories total current month

Post by Nikolay »

Before use please update lines 35, 36 of SQL script

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
Attachments
Categories total current month v2.grm
(2.37 KiB) Downloaded 601 times
Post Reply