Subtract two fields

[demopro]

Hello,

I am running an update command on two columns in a table and have stumbled onto a problem. How can I subtract two fields from each other when the first field is a negative and the second a positive.

EX: -1200.02 – (+2200.02) = (-3400.04)

I cannot change the position of these fields otherwise I would make it:

EX: 2200.02 – 1200.02 = 1000


Here is the command I am using:

UPDATE My_Temp SET [Diff] = ((Cost) - (FYCost))

Does SQL have a function for this or will I have to create one?

Thanks,
Demopro

 

[gmmastros]

I don't really understand what you are trying to do here, so I'll just suggest that you look at the absolute value function. Maybe it will help. Here is an example:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([COLOR=blue]Cost[/color] [COLOR=blue]Numeric[/color](10,2), FYCost [COLOR=blue]Numeric[/color](10,2))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](-1200.02, 2200.02)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1200.02, -2200.02)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](-1200.02, -2200.02)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1200.02, 2200.02)

[COLOR=blue]Select[/color] [COLOR=#FF00FF]abs[/color]([COLOR=#FF00FF]abs[/color]([COLOR=blue]Cost[/color])-[COLOR=#FF00FF]abs[/color](fycost))
[COLOR=blue]From[/color]   @Temp

-George

"the screen with the little boxes in the window." - Moron

 

[Qik3Coder]

FYi, it's doing exactly what you told it...

This should get you where you need to go:

Declare @cost int
declare @FYcost int
set @cost = 16
set @fycost = 6
select
case when @cost >= @fycost then @cost else @fyCost end
-
case when @cost >= @fycost then @fyCost else @cost end

-The answer to your problem may not be the answer to your question.