MMEX Discussion Forum

Code: Select all

select 
strftime('%m', TRANSDATE) as Month,
total (case when TRANSCODE='Deposit' then amount else 0 end) as Deposit,
total (case when TRANSCODE='Withdrawal' then amount else 0 end) as Withdrawal
from (select c.TRANSDATE,
c.TRANSCODE,
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'
--and strftime('%m', TRANSDATE)='01' and strftime('%Y', TRANSDATE)='2014'
and TRANSDATE >= date('now','start of month','-2 month') and  TRANSDATE <= date('now','start of month','+1 month','-1 day') ) t
group by strftime('%m', TRANSDATE)

Code: Select all

select
    strftime('%m', TRANSDATE) as Month,
    t.ACCOUNTID,
    a.ACCOUNTNAME,
    total (case when TRANSCODE='Deposit' then amount else 0 end) as Deposit,
    total (case when TRANSCODE='Withdrawal' then amount else 0 end) as Withdrawal
from (
    select 
        c.TRANSDATE,
        c.TRANSCODE,
        c.ACCOUNTID,
        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'
        --and strftime('%m', TRANSDATE)='01' and strftime('%Y', TRANSDATE)='2014'
        and TRANSDATE >= date('now','start of month','-2 month') and  TRANSDATE <= date('now','start of month','+1 month','-1 day') 
) t
left join accountlist_v1 a on a.accountid = t.accountid
group by 
    strftime('%m', TRANSDATE), 
    t.ACCOUNTID
;