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