Some category with transaction, does not appear on all 12 months. (Example: montly salary and others).
Code: Select all
select case t.subcateg when-1 then ca.categname else ca.categname ||':'||sc.subcategname end
category, t.transid, t.categ, total( casestrftime('%m', date('now', 'start of month','-11 month','localtime'))
when month then amount end ) as twe, total( casestrftime('%m', date('now', 'start of month','-10 month','localtime'))
when month then amount end ) as ele, total( casestrftime('%m', date('now', 'start of month','-9 month','localtime'))
when month then amount end ) as ten, total( casestrftime('%m', date('now', 'start of month','-8 month','localtime'))
when month then amount end ) as nin, total( casestrftime('%m', date('now', 'start of month','-7 month','localtime'))
when month then amount end ) as egh, total( casestrftime('%m', date('now', 'start of month','-6 month','localtime'))
when month then amount end ) as sev, total( casestrftime('%m', date('now', 'start of month','-5 month','localtime'))
when month then amount end ) as six, total( casestrftime('%m', date('now', 'start of month','-4 month','localtime'))
when month then amount end ) as fiv, total( casestrftime('%m', date('now', 'start of month','-3 month','localtime'))
when month then amount end ) as fou, total( casestrftime('%m', date('now', 'start of month','-2 month','localtime'))
when month then amount end ) as thr, total( casestrftime('%m', date('now', 'start of month','-1 month','localtime'))
when month then amount end ) as two, total( casestrftime('%m', date('now', 'start of month','-0 month','localtime'))
when month then amount end ) as one, total(amount) asOVERALLfrom( select strftime('%m', TRANSDATE) as month , c.transid,cf.BaseConvRate , c.accountid,c.transcode , caseifnull(c.categid, -1) when -1 then s.categid else c.categid end as
categ , caseifnull(c.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else
ifnull(c.subcategid,-1) end as subcateg , c.payeeid , sum((casec.categid when -1 then splittransamount else transamount end)
* (casetranscode when 'Withdrawal' then - cf.BaseConvRate else
cf.BaseConvRate end) ) amount fromcheckingaccount_v1 c left joinsplittransactions_v1 s on s.transid=c.transid left joinACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID left joincurrencyformats_v1 cf on cf.currencyid=AC.currencyid where transcode!= 'Transfer' and c.status!='V' and ac.status!='Closed' and (date('now','start of month','-11 month','localtime') <= transdate andtransdate < date('now', 'start of month','+1 month','localtime')) and(strftime('%d', TRANSDATE) <= strftime('%m', 'now')) group by month,categ, subcateg ) t left joincategory_v1 ca on ca.categid=t.categ left joinsubcategory_v1 sc on sc.categid=t.categ and sc.subcategid=t.subcateg
group by categoryorder by category