fraction months report
Moderator: Renato
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
-
- 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
- Attachments
-
- test.grm
- (4.17 KiB) Downloaded 685 times
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
-
- 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
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.
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
-
- 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
the sql is brocken
Re: fraction months report
THANKS
WHERE THE CODE IS BROKEN ?
CAN U HELP ME ?
WHERE THE CODE IS BROKEN ?
CAN U HELP ME ?
-
- 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
I can, but I am very busy this and possible next week.
Sorry.
Sorry.
Re: fraction months report
ThanksNikolay wrote:I can, but I am very busy this and possible next week.
Sorry.
Re: fraction months report
May u help me ?