Category stats

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

Category stats

Post by Nikolay »

Code: Select all

select 
categid, subcategid,
case when '01' = strftime('%m', TRANSDATE) then total (amount) else 0 end as Jan,
case when '02' = strftime('%m', TRANSDATE) then total (amount) else 0 end as Feb,
case when '03' = strftime('%m', TRANSDATE) then total (amount) else 0 end as Mar,
case when '04' = strftime('%m', TRANSDATE) then total (amount) else 0 end as Apr,
case when '05' = strftime('%m', TRANSDATE) then total (amount) else 0 end as May,
case when '06' = strftime('%m', TRANSDATE) then total (amount) else 0 end as Jun
from (select c.TRANSDATE,
case when c.categid=-1 then s.categid else c.categid end as categid,
case when c.categid=-1 then s.subcategid else c.subcategid end as subcategid,
case when c.TRANSCODE='Deposit' then 1 else -1 end 
    * case when c.categid=-1 then s.splittransamount else c.transamount end as amount

from checkingaccount_v1 c 
left join splittransactions_v1 s on s.transid = c.transid
where c.TRANSCODE <>'Transfer') t
group by categid, subcategid
Post Reply