fraction months report
Moderator: Renato
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
fraction months report
Is available a report of current and lasts months for specific or all categories.
report like this:
today is 13 june 2015, and the report have to report revenue and expences group by category or payee, for any fraction of months from 1 day of month to 13.
thanks
giorgio
report like this:
today is 13 june 2015, and the report have to report revenue and expences group by category or payee, for any fraction of months from 1 day of month to 13.
thanks
giorgio
-
- 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
- Attachments
-
- test.grm
- (4.17 KiB) Downloaded 703 times
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
I tested the report. It ok. Is what I looking for.
In some months repor, does not appear some transaction.
Seems to be random error....
Help me
In some months repor, does not appear some transaction.
Seems to be random error....
Help me
-
- 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
It may be depend on time zone.
The 'localtime' instruction should be used for date functions.
The 'localtime' instruction should be used for date functions.
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
So, how, can I try to fix it ?
Some category with transaction, does not appear on all 12 months. (Example: montly salary and others).
Some others category on random months does not apper.
thanks
Giorgio
SQL code as follow:
Some category with transaction, does not appear on all 12 months. (Example: montly salary and others).
Some others category on random months does not apper.
thanks
Giorgio
SQL code as follow:
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
-
- 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
the sql is brocken
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
THANKS
WHERE THE CODE IS BROKEN ?
CAN U HELP ME ?
WHERE THE CODE IS BROKEN ?
CAN U HELP ME ?
-
- 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
I can, but I am very busy this and possible next week.
Sorry.
Sorry.
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
ThanksNikolay wrote:I can, but I am very busy this and possible next week.
Sorry.
-
- New User
- Posts: 19
- Joined: Mon Jun 01, 2015 2:36 pm
- Are you a spam bot?: No
Re: fraction months report
May u help me ?
-
- 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
Hi.
I'll try to investigate what the problem with the report.
I'll try to investigate what the problem with the report.
-
- 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