CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
trevorc
trevorc

Reputation: 3031

How to subtract negative decimals in Sql Server using Insert Into

I am trying to write a query that tracks the history of decimal values in 2 tables.

Table1

PK  Num1   D1        D2
1   1      -0.00375  -0.005
2   1      -0.00325

Table2

PK   Num1   D1        D2
1    1      -0.00375  -0.0025
2    2      -0.005

Table3 (The History Table)

Num1  OldD2     NewD2     D2Difference
1     -0.005  -0.0025  0.0000//This is where I am having trouble

My query will add the 2 D2 values but not subtract. It always returns 0.00 Here is my query.

BEGIN TRANSACTION
INSERT INTO Table3
SELECT Table1.Num1, Table1.D2, Table2.D2, (Table1.D2 - Table2.D2)
FROM Table1
INNER JOIN Table2 ON Table1.Num1 = Table2.Num1 AND Table1.D1 = Table2.D1
COMMIT

Again, I can add to the 2 decimals but subtracting always returns 0.00. I know I'm missing the trees through the forest (or is it the other way around?).

NOTE: All decimals are (5,5) and the joins are a result of tables 1 and 2 having a unique D1 value for each Num1.

Upvotes: 2

Views: 1127

Answers (1)

sll
sll

Reputation: 62544

Since you are selecting only D1 which are equal in both tables - substract operation will return 0.00

Just remove JOIN condition AND Table1.D1 = Table2.D1

EDIT: Note

As question was changed my answer is not obvious because initial sql query was changed

Upvotes: 3

Related Questions