PAYEE TREND MODIFICATION

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

Moderator: Renato

Post Reply
fabianfp
New MMEX User
Posts: 10
Joined: Sun Sep 28, 2014 10:29 pm
Are you a spam bot?: No

PAYEE TREND MODIFICATION

Post by fabianfp »

Code: Select all

[table]-- TODO: Update payee name in line 19. select strftime('%Y', t.TRANSDATE) as YEAR    , total(case when '01' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Jan    , total(case when '02' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Feb    , total(case when '03' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Mar    , total(case when '04' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Apr    , total(case when '05' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as May    , total(case when '06' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Jun    , total(case when '07' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Jul    , total(case when '08' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Aug    , total(case when '09' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Sep    , total(case when '10' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Oct    , total(case when '11' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Nov    , total(case when '12' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Decfrom CHECKINGACCOUNT_V1 as tinner join ACCOUNTLIST_V1 as a on a.ACCOUNTID = t.ACCOUNTIDinner join CURRENCYFORMATS_V1 as c on c.CURRENCYID = a.CURRENCYIDinner join  PAYEE_V1 as p on t.PAYEEID = p.PAYEEIDwhere p.PAYEENAME = 'Payee1'    and t.TRANSCODE='Withdrawal'    and t.STATUS <> 'V'group by yearorder by year desc;[/table]
In the Payee Trend example. I wan´t to modify the code for calculate Withdrawal - Deposit for especific Category.

[table]where p.PAYEENAME = 'Payee1' and t.TRANSCODE='Withdrawal' and t.STATUS <> 'V'[/table]

Can you HELP ME? I tried but i can´t do it.
Thanks.
Post Reply