How do I change the "sign" of a number in a case statement ?

Question

Post reply

How do I change the "sign" of a number in a case statement ?

Craig Lloyd

SSC Journeyman

Points: 84
More actions
March 22, 2007 at 7:47 am

#172587

Hi,
the values created in my view by this select statement carry through negative numbers as "Scrap" bookings on our system are always negative adjustments.
CAST(sum(case when datepart(yyyy,dateadd(mm,-1,getdate())) = TrnYear and datepart(mm,dateadd(mm,-1,getdate())) = TrnMonth then TrnQty else 0 end) as int) as "CurMonth-1",
How can I add something here to take away the "-" from the number, I need to do this because the Crystal report I'm using to produce a graph needs positive numbers else the graph goes the wrong way.
Any help appreciated,
Craig Lloyd

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Craig,

Can you try this logic to test?

declare @num real

set @num = -10

select cast(replace(convert(varchar, @num), '-', '') as int)

you can substitue your column name for @num local variable. if your column is already an int datatype, then you can use

select cast(replace(convert(varchar, <your column>, '-', '') as int)

Thanks

Lucky

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

multiply the value times -1 to convert it to a positive value

CAST(sum(case when datepart(yyyy,dateadd(mm,-1,getdate())) = TrnYear and datepart(mm,dateadd(mm,-1,getdate())) = TrnMonth then TrnQty*-1 else 0 end) as int) as "CurMonth-1",

Craig Lloyd SSC Journeyman Points: 84 More actions · Answer 3

yey hey,

cheers guys,

Thanks, the *-1 was the easiest to implement in my script, thanks a lot.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Craig

Yes, since all your values are negative, this will work:

UPDATE MyTable SET MyNum = MyNum * -1

WHERE <whatever>

But if you didn't know whether the values would be be positive or negative, you could use the ABS function, which returns the non-negative value of a number:

UPDATE MyTable SET MyNum = ABS(MyNum)

WHERE <whatever>

John

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 5

Hi,

there is a built in function to do this. look up abs in BOL.

HTH

Paul

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

In this case using ABS would create errors in your graph if there were positive numbers because -100 and +100 would become equal. Using *-1 just reverses sign and the direction of the graph.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

True, depending on the requirement. Sometimes you are only interested in the size of a number, which, in the case of a scalar, means its distance from zero. For example, if you were measuring the height of stalagmites and stalactites, you might have stalagmites with positive values since their tip is above their base, but stalactites would have negative values because the tip is below the base. However, in one report, you may be interested only in the absolute length of the rock formation, in which case you would use the ABS function.

John