fraction months report

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Nikolay
MMEX 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

Post by Nikolay »

Hi.

I'll try to investigate what the problem with the report.
Nikolay
MMEX 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

Post by Nikolay »

Could you try this report? 
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
Nikolay
MMEX 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

Post by Nikolay »

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
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

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
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

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
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

software on main desk If I execute the query respond: "SQL logic error or missing database[1]: near "with": syntax error"
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

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 :D



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
Post Reply