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)
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
You do not have the required permissions to view the files attached to this post.
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');
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'
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