Search code examples
sqlsql-servernegative-number

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)
Solution

  • 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.