Subtract two numeric columns

Question

Is there a way to find the subtraction of two columns where each column might be a positive or negative values.

eg.

+------+------+------+
| colA | colB | colC |
+------+------+------+
| -100 |  100 |    0 |
|  200 | -200 |    0 |
| -300 | -400 | -100 |
|  200 |  300 |  100 |
+------+------+------+

Answer 1

If [colC] is what you're trying to get, you're just doing simple addition, not really subtraction:

• [-100] + [100] = [0]

• [-300] + [-400] = [-700]

  SELECT colA , colB , TotalValue = colA + colB FROM yourTable;

Answer 2

How about this?

• If both columns are position, then we take the answer to be an absolute value, because it would be a positive integer like this:
  • abs(colA - colB)
• If both columns are negative, we can make ONE of the columns an absolute value and then add them like this:
  • abs(colA) + colB
• If either logic is false, then it is likely one column is negative and the other is positive, so we can use RussellFox's logic:
  • colA + colB

So put it all together:

select colA
       ,colB
       ,colC = case when colA > 0 and colB > 0 then abs(colA - colB)
                    when colA < 0 and colB < 0 then abs(colA) + colB
                    else colA + colB
               end
from mytable

Here's a rextester sample for you to play with

Answer 3

It really comes down to the precise requirement you have in mind for building the logic.

select
, colA
, colB
, abs(abs(colA) - abs(colB)) as Abs_Value
, abs(colA) - abs(colB) as Abs_Value_Diff
, colB - abs(colA) as Abs_Value_Diff2

, case when colA >= 0 and colB >= 0 then colB - colA
    when colA < 0 and colB < 0 then colB - colA
    when colA < 0 and colB >= 0 then colB - abs(colA)
    when colA >= 0 and colB < 0 then abs(colB) - colA
    else colB - colA 
    end as Abs_Value_Diff3
from 
test_table
order by TestField1;