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