Fri Feb 20, 2015 4:16 pm
Dear All,
I try to make a credit card monthly statement from 6th of Jan 2015 to 5th Feb 2015. Here i want to make 2 columns as Expenses & Deposits with at last total of each. Here credit card payments are done by transferring amount from another a/cs & receives some petrol surcharge amount as deposit. I try to produce the sql code as below but i am unable to put the petrol surchage entry list in the Deposit column . Here 4 is my cc a/c id.
Please someone help me to complete.
I try to make a credit card monthly statement from 6th of Jan 2015 to 5th Feb 2015. Here i want to make 2 columns as Expenses & Deposits with at last total of each. Here credit card payments are done by transferring amount from another a/cs & receives some petrol surcharge amount as deposit. I try to produce the sql code as below but i am unable to put the petrol surchage entry list in the Deposit column . Here 4 is my cc a/c id.
Please someone help me to complete.
- Code: Select all
select strftime('%m', TRANSDATE) as MONTH
,strftime('%Y', TRANSDATE) as YEAR
, strftime('%d-%m-%Y',transdate) as Date
--, accountid
--, toaccountid
, transcode
,ROUND((case accountid when 4 then
case transcode when 'Deposit' then
TRANSAMOUNT
ELSE
-TRANSAMOUNT
END
end ),2) As Expenses
,ROUND((case toaccountid when 4 then
case transcode when 'Transfer' then TRANSAMOUNT
ELSE
-TRANSAMOUNT
END
end ),2) as Deposit
from checkingaccount_v1
where transdate>= "2015-01-06" and transdate <= "2015-02-05"
AND (accountid=4 OR toaccountid=4)
order by YEAR ;
- Attachments
-
Output table.JPG
- (81.73 KiB) Downloaded 892 times