Hi.
I'll try to investigate what the problem with the report.
fraction months report
Moderator: Renato
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: fraction months report
Could you try this report?
It's the same but has one small fix in line
It's the same but has one small fix in line
Code: Select all
strftime('%m', TRANSDATE, 'localtime') as month
Last edited by Nikolay on Mon Jun 27, 2016 7:55 am, edited 2 times in total.
Reason: attachment is missing
Reason: attachment is missing
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: fraction months report
For some reasons I can't attache the file.
Code: Select all
with t AS (
select
strftime('%m', TRANSDATE, 'localtime') as month
, c.transid, cf.BaseConvRate
, c.accountid, c.transcode
, case ifnull(c.categid, -1) when -1 then s.categid else c.categid end as categ
, case ifnull(c.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(c.subcategid,-1) end as subcateg
, c.payeeid
, sum((case c.categid when -1 then splittransamount else transamount end)
* (case transcode when 'Withdrawal' then - cf.BaseConvRate else cf.BaseConvRate end)
) amount
, sum((case c.categid when -1 then splittransamount else transamount end)
* (case transcode when 'Withdrawal' then - cf.BaseConvRate else 0 end)
) amountWithdraw
, sum((case c.categid when -1 then splittransamount else transamount end)
* (case transcode when 'Withdrawal' then 0 else cf.BaseConvRate end)
) amountDeposit
from checkingaccount_v1 c
left join splittransactions_v1 s on s.transid=c.transid
left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID
left join currencyformats_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
and transdate < date('now', 'start of month','+1 month','localtime'))
group by month, categ, subcateg
)
select
case t.subcateg when -1 then ca.categname else ca.categname ||':'||sc.subcategname end category
, t.transid, t.categ
, total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amount end ) as twe
, total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amount end ) as ele
, total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amount end ) as ten
, total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amount end ) as nin
, total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amount end ) as egh
, total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amount end ) as sev
, total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amount end ) as six
, total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amount end ) as fiv
, total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amount end ) as fou
, total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amount end ) as thr
, total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amount end ) as two
, total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amount end ) as one
, total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amountWithdraw end ) as WITH_twe
, total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amountWithdraw end ) as WITH_ele
, total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amountWithdraw end ) as WITH_ten
, total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amountWithdraw end ) as WITH_nin
, total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amountWithdraw end ) as WITH_egh
, total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amountWithdraw end ) as WITH_sev
, total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amountWithdraw end ) as WITH_six
, total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amountWithdraw end ) as WITH_fiv
, total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amountWithdraw end ) as WITH_fou
, total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amountWithdraw end ) as WITH_thr
, total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amountWithdraw end ) as WITH_two
, total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amountWithdraw end ) as WITH_one
, total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amountDeposit end ) as DEP_twe
, total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amountDeposit end ) as DEP_ele
, total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amountDeposit end ) as DEP_ten
, total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amountDeposit end ) as DEP_nin
, total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amountDeposit end ) as DEP_egh
, total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amountDeposit end ) as DEP_sev
, total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amountDeposit end ) as DEP_six
, total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amountDeposit end ) as DEP_fiv
, total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amountDeposit end ) as DEP_fou
, total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amountDeposit end ) as DEP_thr
, total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amountDeposit end ) as DEP_two
, total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amountDeposit end ) as DEP_one
, total(amount) as OVERALL
from t
left join category_v1 ca on ca.categid=t.categ
left join subcategory_v1 sc on sc.categid=t.categ and sc.subcategid=t.subcateg
group by category
order by category
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
Nikolay wrote:For some reasons I can't attache the file.
Code: Select all
with t AS ( select strftime('%m', TRANSDATE, 'localtime') as month , c.transid, cf.BaseConvRate , c.accountid, c.transcode , case ifnull(c.categid, -1) when -1 then s.categid else c.categid end as categ , case ifnull(c.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(c.subcategid,-1) end as subcateg , c.payeeid , sum((case c.categid when -1 then splittransamount else transamount end) * (case transcode when 'Withdrawal' then - cf.BaseConvRate else cf.BaseConvRate end) ) amount , sum((case c.categid when -1 then splittransamount else transamount end) * (case transcode when 'Withdrawal' then - cf.BaseConvRate else 0 end) ) amountWithdraw , sum((case c.categid when -1 then splittransamount else transamount end) * (case transcode when 'Withdrawal' then 0 else cf.BaseConvRate end) ) amountDeposit from checkingaccount_v1 c left join splittransactions_v1 s on s.transid=c.transid left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID left join currencyformats_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 and transdate < date('now', 'start of month','+1 month','localtime')) group by month, categ, subcateg ) select case t.subcateg when -1 then ca.categname else ca.categname ||':'||sc.subcategname end category , t.transid, t.categ , total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amount end ) as twe , total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amount end ) as ele , total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amount end ) as ten , total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amount end ) as nin , total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amount end ) as egh , total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amount end ) as sev , total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amount end ) as six , total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amount end ) as fiv , total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amount end ) as fou , total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amount end ) as thr , total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amount end ) as two , total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amount end ) as one , total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amountWithdraw end ) as WITH_twe , total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amountWithdraw end ) as WITH_ele , total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amountWithdraw end ) as WITH_ten , total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amountWithdraw end ) as WITH_nin , total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amountWithdraw end ) as WITH_egh , total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amountWithdraw end ) as WITH_sev , total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amountWithdraw end ) as WITH_six , total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amountWithdraw end ) as WITH_fiv , total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amountWithdraw end ) as WITH_fou , total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amountWithdraw end ) as WITH_thr , total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amountWithdraw end ) as WITH_two , total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amountWithdraw end ) as WITH_one , total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amountDeposit end ) as DEP_twe , total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amountDeposit end ) as DEP_ele , total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amountDeposit end ) as DEP_ten , total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amountDeposit end ) as DEP_nin , total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amountDeposit end ) as DEP_egh , total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amountDeposit end ) as DEP_sev , total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amountDeposit end ) as DEP_six , total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amountDeposit end ) as DEP_fiv , total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amountDeposit end ) as DEP_fou , total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amountDeposit end ) as DEP_thr , total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amountDeposit end ) as DEP_two , total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amountDeposit end ) as DEP_one , total(amount) as OVERALL from t left join category_v1 ca on ca.categid=t.categ left join subcategory_v1 sc on sc.categid=t.categ and sc.subcategid=t.subcateg group by category order by category
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
I paste all code on sql tab.
Report manager Manager say SQL SYNTAX ERROR
Where I have to insert accont name, or code repot for all accont ?
thanks
Giorgio
Report manager Manager say SQL SYNTAX ERROR
Where I have to insert accont name, or code repot for all accont ?
thanks
Giorgio
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
software on main desk If I execute the query respond: "SQL logic error or missing database[1]: near "with": syntax error"
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
I EXECUTE FOLLOWING CODE, BUT SEEM BE MONTLY TRANSACTION AND NOT FRACTION MONTH.
AND THERE ARE TREE SERIES OF DATA (36 + OVERALL) THE SERIES ARE "TWE", "WITH_12", "DEP_12". TRANSACTION ARE DUPLICATED ON "TWE", "WITH_12".
I'M CONFUSED
PLEASE HELP ME
TKS
with t AS ( select strftime('%m', TRANSDATE, 'localtime') 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((case c.categid when -1 then splittransamount else transamount
end)
*(case transcode when 'Withdrawal' then - cf.BaseConvRate else
cf.BaseConvRate end) )amount ,sum((case c.categid when -1 then splittransamount else transamount
end)
*(case transcode when 'Withdrawal' then - cf.BaseConvRate else 0 end) )amountWithdraw ,sum((case c.categid when -1 then splittransamount else transamount
end)
*(case transcode when 'Withdrawal' then 0 else cf.BaseConvRate end) )amountDeposit 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 wheretranscode != 'Transfer' and c.status!='V' andac.status !='Closed' and(date('now', 'start of month','-11 month','localtime') <=
transdate andtransdate < date('now', 'start of month','+1 month','localtime')) group bymonth, categ, subcateg ) select case t.subcategwhen -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( casestrftime('%m', date('now', 'start of month','-11 month','localtime'))
when month then amountWithdraw end ) as WITH_12 , total( casestrftime('%m', date('now', 'start of month','-10 month','localtime'))
when month then amountWithdraw end ) as WITH_11 , total( casestrftime('%m', date('now', 'start of month','-9 month','localtime'))
when month then amountWithdraw end ) as WITH_10 , total( casestrftime('%m', date('now', 'start of month','-8 month','localtime'))
when month then amountWithdraw end ) as WITH_9 , total( casestrftime('%m', date('now', 'start of month','-7 month','localtime'))
when month then amountWithdraw end ) as WITH_8 , total( casestrftime('%m', date('8', 'start of month','-6 month','localtime'))
when month then amountWithdraw end ) as WITH_7 , total( casestrftime('%m', date('now', 'start of month','-5 month','localtime'))
when month then amountWithdraw end ) as WITH_6 , total( casestrftime('%m', date('now', 'start of month','-4 month','localtime'))
when month then amountWithdraw end ) as WITH_5 , total( casestrftime('%m', date('now', 'start of month','-3 month','localtime'))
when month then amountWithdraw end ) as WITH_4 , total( casestrftime('%m', date('now', 'start of month','-2 month','localtime'))
when month then amountWithdraw end ) as WITH_3 , total( casestrftime('%m', date('now', 'start of month','-1 month','localtime'))
when month then amountWithdraw end ) as WITH_2 , total( casestrftime('%m', date('now', 'start of month','-0 month','localtime'))
when month then amountWithdraw end ) as WITH_1 , total( casestrftime('%m', date('now', 'start of month','-11 month','localtime'))
when month then amountDeposit end ) as DEP_12 , total( casestrftime('%m', date('now', 'start of month','-10 month','localtime'))
when month then amountDeposit end ) as DEP_11 , total( casestrftime('%m', date('now', 'start of month','-9 month','localtime'))
when month then amountDeposit end ) as DEP_10 , total( casestrftime('%m', date('now', 'start of month','-8 month','localtime'))
when month then amountDeposit end ) as DEP_9 , total( casestrftime('%m', date('now', 'start of month','-7 month','localtime'))
when month then amountDeposit end ) as DEP_8 , total( casestrftime('%m', date('now', 'start of month','-6 month','localtime'))
when month then amountDeposit end ) as DEP_7 , total( casestrftime('%m', date('now', 'start of month','-5 month','localtime'))
when month then amountDeposit end ) as DEP_6 , total( casestrftime('%m', date('now', 'start of month','-4 month','localtime'))
when month then amountDeposit end ) as DEP_5 , total( casestrftime('%m', date('now', 'start of month','-3 month','localtime'))
when month then amountDeposit end ) as DEP_4 , total( casestrftime('%m', date('now', 'start of month','-2 month','localtime'))
when month then amountDeposit end ) as DEP_3 , total( casestrftime('%m', date('now', 'start of month','-1 month','localtime'))
when month then amountDeposit end ) as DEP_2 , total( casestrftime('%m', date('now', 'start of month','-0 month','localtime'))
when month then amountDeposit end ) as DEP_1 , total(amount)as OVERALL from 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 bycategory order bycategory
AND THERE ARE TREE SERIES OF DATA (36 + OVERALL) THE SERIES ARE "TWE", "WITH_12", "DEP_12". TRANSACTION ARE DUPLICATED ON "TWE", "WITH_12".
I'M CONFUSED
PLEASE HELP ME
TKS
with t AS ( select strftime('%m', TRANSDATE, 'localtime') 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((case c.categid when -1 then splittransamount else transamount
end)
*(case transcode when 'Withdrawal' then - cf.BaseConvRate else
cf.BaseConvRate end) )amount ,sum((case c.categid when -1 then splittransamount else transamount
end)
*(case transcode when 'Withdrawal' then - cf.BaseConvRate else 0 end) )amountWithdraw ,sum((case c.categid when -1 then splittransamount else transamount
end)
*(case transcode when 'Withdrawal' then 0 else cf.BaseConvRate end) )amountDeposit 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 wheretranscode != 'Transfer' and c.status!='V' andac.status !='Closed' and(date('now', 'start of month','-11 month','localtime') <=
transdate andtransdate < date('now', 'start of month','+1 month','localtime')) group bymonth, categ, subcateg ) select case t.subcategwhen -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( casestrftime('%m', date('now', 'start of month','-11 month','localtime'))
when month then amountWithdraw end ) as WITH_12 , total( casestrftime('%m', date('now', 'start of month','-10 month','localtime'))
when month then amountWithdraw end ) as WITH_11 , total( casestrftime('%m', date('now', 'start of month','-9 month','localtime'))
when month then amountWithdraw end ) as WITH_10 , total( casestrftime('%m', date('now', 'start of month','-8 month','localtime'))
when month then amountWithdraw end ) as WITH_9 , total( casestrftime('%m', date('now', 'start of month','-7 month','localtime'))
when month then amountWithdraw end ) as WITH_8 , total( casestrftime('%m', date('8', 'start of month','-6 month','localtime'))
when month then amountWithdraw end ) as WITH_7 , total( casestrftime('%m', date('now', 'start of month','-5 month','localtime'))
when month then amountWithdraw end ) as WITH_6 , total( casestrftime('%m', date('now', 'start of month','-4 month','localtime'))
when month then amountWithdraw end ) as WITH_5 , total( casestrftime('%m', date('now', 'start of month','-3 month','localtime'))
when month then amountWithdraw end ) as WITH_4 , total( casestrftime('%m', date('now', 'start of month','-2 month','localtime'))
when month then amountWithdraw end ) as WITH_3 , total( casestrftime('%m', date('now', 'start of month','-1 month','localtime'))
when month then amountWithdraw end ) as WITH_2 , total( casestrftime('%m', date('now', 'start of month','-0 month','localtime'))
when month then amountWithdraw end ) as WITH_1 , total( casestrftime('%m', date('now', 'start of month','-11 month','localtime'))
when month then amountDeposit end ) as DEP_12 , total( casestrftime('%m', date('now', 'start of month','-10 month','localtime'))
when month then amountDeposit end ) as DEP_11 , total( casestrftime('%m', date('now', 'start of month','-9 month','localtime'))
when month then amountDeposit end ) as DEP_10 , total( casestrftime('%m', date('now', 'start of month','-8 month','localtime'))
when month then amountDeposit end ) as DEP_9 , total( casestrftime('%m', date('now', 'start of month','-7 month','localtime'))
when month then amountDeposit end ) as DEP_8 , total( casestrftime('%m', date('now', 'start of month','-6 month','localtime'))
when month then amountDeposit end ) as DEP_7 , total( casestrftime('%m', date('now', 'start of month','-5 month','localtime'))
when month then amountDeposit end ) as DEP_6 , total( casestrftime('%m', date('now', 'start of month','-4 month','localtime'))
when month then amountDeposit end ) as DEP_5 , total( casestrftime('%m', date('now', 'start of month','-3 month','localtime'))
when month then amountDeposit end ) as DEP_4 , total( casestrftime('%m', date('now', 'start of month','-2 month','localtime'))
when month then amountDeposit end ) as DEP_3 , total( casestrftime('%m', date('now', 'start of month','-1 month','localtime'))
when month then amountDeposit end ) as DEP_2 , total( casestrftime('%m', date('now', 'start of month','-0 month','localtime'))
when month then amountDeposit end ) as DEP_1 , total(amount)as OVERALL from 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 bycategory order bycategory