Page 1 of 1
Not being able to reverse the sign in a Numeric column
Posted: Sun Apr 03, 2016 10:12 pm
by Vara
Based on the condition of 'Deposit' and category 'OUTFLOWS', i want to reverse the sign in CHECKINGACCOUNT_V1 for transamount to be negative otherwise keep it positive However when I execute the query it does nothing to the sign. It is as though it is ignoring the CASE statement. See SQL below (SQLtests OK but results not what I expected)
Code: Select all
select transcode,categid, subcategid
, CASE categid WHEN '17' and transcode = 'Deposit' then -1*transamount else transamount end as actuals
from checkingaccount_v1
where subcategid not in ('-1', '98','99');
See results spreadsheet attached. The shaded area was expected to have negative amounts but that was not the case!
I would be grateful if anybody can guide me on how to get this running properly.
Thanks
Vara
Re: Not being able to reverse the sign in a Numeric column
Posted: Mon Apr 04, 2016 7:46 am
by Nikolay
Code: Select all
select t.transcode, t.categid, t.subcategid
, case t.SUBCATEGID when -1 then c.CATEGNAME else c.CATEGNAME||':'|| s.SUBCATEGNAME end as Category
, CASE t.TRANSCODE WHEN 'Deposit' then t.transamount else -t.transamount end as actuals
from checkingaccount_v1 t
inner join CATEGORY_V1 c on t.CATEGID=c.CATEGID
left join SUBCATEGORY_V1 s on t.SUBCATEGID=s.SUBCATEGID and s.CATEGID=c.CATEGID
where t.subcategid not in ('-1', '98','99');
Re: Not being able to reverse the sign in a Numeric column
Posted: Mon Apr 04, 2016 8:33 am
by Nikolay
The same query but with 'WITH' usage
Code: Select all
with c AS (select c.CATEGID, s.SUBCATEGID
, c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
from CATEGORY_V1 c, SUBCATEGORY_V1 s
where c.CATEGID = s.CATEGID
union all
select CATEGID, -1 SUBCATEGID, CATEGNAME from CATEGORY_V1)
select t.transcode, t.categid, t.subcategid
, CASE t.TRANSCODE WHEN 'Deposit' then t.transamount else -t.transamount end as actuals
, c.CATEGORY
from checkingaccount_v1 t
inner join c on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID
--where c.CATEGORY = 'Transfer'
Re: Not being able to reverse the sign in a Numeric column
Posted: Mon Apr 04, 2016 11:10 am
by Nikolay
Just created sql script to get all transactions included split.
Code: Select all
with c AS (select c.CATEGID, s.SUBCATEGID
, c.CATEGNAME||':'||s.SUBCATEGNAME CATEGORY
from CATEGORY_V1 c, SUBCATEGORY_V1 s
where c.CATEGID = s.CATEGID
union all
select CATEGID, -1 SUBCATEGID, CATEGNAME from CATEGORY_V1),
t as (select t.TRANSDATE , t.transcode, t.PAYEEID
, case ifnull(t.categid, -1) when -1 then s.categid else t.categid end categid
, case when ifnull(t.categid, -1) = -1 AND ifnull(t.subcategid, -1) = -1 then s.subcategid else t.subcategid end subcategid
, (case ifnull(s.splittransid, -1) when -1 then t.transamount else s.splittransamount end)
*(case t.transcode when 'Deposit' then 1 else -1 end) as Amount
from checkingaccount_v1 t
LEFT JOIN splittransactions_v1 s ON s.transid=t.transid )
select t.TRANSDATE, c.CATEGORY, t.AMOUNT
from t
inner join c on c.CATEGID = t.CATEGID and t.SUBCATEGID=c.SUBCATEGID
Re: Not being able to reverse the sign in a Numeric column
Posted: Thu Apr 07, 2016 5:07 am
by Vara
Thanks alot