Not being able to reverse the sign in a Numeric column

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)
Post Reply
Vara
New MMEX User
Posts: 6
Joined: Thu Mar 24, 2016 5:19 am
Are you a spam bot?: No

Not being able to reverse the sign in a Numeric column

Post 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
Attachments
Vara SQL results for Forum.xlsx
(20.21 KiB) Downloaded 208 times

Nikolay
MMEX Developer
Posts: 1295
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Not being able to reverse the sign in a Numeric column

Post 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');

Nikolay
MMEX Developer
Posts: 1295
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Not being able to reverse the sign in a Numeric column

Post 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'

Nikolay
MMEX Developer
Posts: 1295
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Not being able to reverse the sign in a Numeric column

Post 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

Vara
New MMEX User
Posts: 6
Joined: Thu Mar 24, 2016 5:19 am
Are you a spam bot?: No

Re: Not being able to reverse the sign in a Numeric column

Post by Vara »

Thanks alot

Post Reply