CODEWITHSUNDEEP
sqlsql-servernegative-number
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Ignoring negative number sign SQL SERVER

I am looking to return amounts that are different from each other. I am dealing with this data:

Amount  InvoiceNumber   Amount2    InvoiceNumber2
1233.99       1          -1233.99        1
567.99        2          567.99          2
-3335.99      3          -3335.99        3
-3335.99      4          4000.99         4

Now, i am looking for a result such as

Amount  InvoiceNumber   Amount2 InvoiceNumber2
-3335.99       4        4000.99       4

I want to return values that are different from each other by ignoring the - sign but still keeping the - sign in the result. E.G. I want to keep 1233 and -1233 because this could reflect a credit etc...

The date i am actually dealing with contains a decimal place and i cant use RIGHT syntax because the size of the number varies. I thought i could use left syntax but i need to skip the - sign.

Can anyone give me any advice? thanks

sql fiddle - http://sqlfiddle.com/#!6/31ee0/7

Attempts:

SELECT I.Amount, I.InvoiceNumber,
I2.Amount2, I2.InvoiceNumber2
FROM Invoice I 
JOIN Invoice2 I2 ON I.InvoiceNumber = I2.InvoiceNumber2
WHERE RIGHT(I.Amount,3) <> RIGHT(I2.Amount2,3)

Upvotes: 2

Views: 11371

Answers (2)

DineshDB
DineshDB

Reputation: 6193

You can use ABS in SQL Server, to skip the -sign.

SELECT I.Amount, I.InvoiceNumber,
I2.Amount2, I2.InvoiceNumber2
FROM Invoice I 
JOIN Invoice2 I2 ON I.InvoiceNumber = I2.InvoiceNumber2
WHERE ABS(I.Amount) <> ABS(I2.Amount2)

Hope this helps you.

Upvotes: 3

Ven
Ven

Reputation: 2014

Remove where clause and u can achieve it this way

SELECT I.Amount, I.InvoiceNumber,
I2.Amount2, I2.InvoiceNumber2
FROM Invoice I 
JOIN Invoice2 I2 ON I.InvoiceNumber = I2.InvoiceNumber2
and I.Amount <> I2.Amount2

-- If you want to ignore -sign use ABS function 

SELECT I.Amount, I.InvoiceNumber,
I2.Amount2, I2.InvoiceNumber2
FROM Invoice I 
JOIN Invoice2 I2 ON I.InvoiceNumber = I2.InvoiceNumber2
and abs(I.Amount) <> abs(I2.Amount2)

Upvotes: 1

Related Questions