fraction months report

17 posts
Re: fraction months report

Nikolay
MMEX Developer

Posts: 2279
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Hi.

I'll try to investigate what the problem with the report.
Re: fraction months report

Nikolay
MMEX Developer

Posts: 2279
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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 1:55 am, edited 2 times in total.
Reason: attachment is missing
Re: fraction months report

Nikolay
MMEX Developer

Posts: 2279
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
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
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
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
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
software on main desk If I execute the query respond: "SQL logic error or missing database[1]: near "with": syntax error"
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
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
Who is online

Users browsing this forum: No registered users and 1 guest