Prev: Compare Data
Next: I am trying to match a negative number to its exact opposite posit
From: Sh0t2bts on 23 Jun 2010 09:48
Hi All,

I am trying to match a negative number to its exact opposite positive
number, in my table ill use -1520.90 (Variable A) to match 1520.90
(Variable B) as the match I want.

First off I did a little maths and subtracted Variable A from its self
twice A-A-A = 1520.90 (Variable B)
In my Where cause wrote where Variable A = Variable B but I get
nothing back.

I have tried casting the values as Decimal and floats but still have
no luck.

If I copy the result into Excel and use the IF statement to match them
I get a match.

Below is my Table and statement, I only expect to get a match on lines
5 and 11
0 DocNo Z1POLine Z1Value Z1MatchedOn
1 6100263619 122698/1 -1550.87 1550.87
2 6100261249 122698/1 414.45 -414.45
3 6100261247 122698/2 391.97 -391.97
4 6100261244 122698/2 2137.5 -2137.5
5 6100255433 122698/2 1520.9 -1520.9
6 6100261245 122698/2 884.36 -884.36
7 6100261246 122698/1 708.32 -708.32
8 6100261003 122698/1 -1387.42 1387.42
9 6100263620 122698/2 -292.81 292.81
10 6100261248 122698/1 393.42 -393.42
11 6100255330 122698/2 -1520.9 1520.9
12 6100261004 122698/2 -3770.6 3770.6


SELECT DocNo, Z1POLine, Z1Value, Z1MatchedOn FROM(
SELECT Z1.DocNo, Z1.Type, Z1.text as Z1POLine, Z2.text as Z2POLine,
Z1.Value as Z1Value, Z2.Value as Z2Value, Z2.Value-Z2.Value-Z2.Value
as Z2MatchedOn, Z1.Value-Z1.Value-Z1.Value as Z1MatchedOn
FROM Z1
LEFT OUTER JOIN Z2 on Z1.text = Z2.text and Z1.Value = Z2.Value -
Z2.Value-Z2.Value
WHERE Z1.Text like '122698/%' and Z2.text is null
) as Data
WHERE Z1POLine = Z1POLine and Cast(Z1Value as decimal) =
Cast(Z1MatchedOn as decimal)

Any Help is really appreachiated
From: Bob Barrows on 23 Jun 2010 09:55
Sh0t2bts wrote:
> Hi All,
>
> I am trying to match a negative number to its exact opposite positive
> number, in my table ill use -1520.90 (Variable A) to match 1520.90
> (Variable B) as the match I want.

Ever hear of "absolute value"? :-)
In T-SQL, the ABS() function returns the absolute value of a number (its
distance from 0)

WHERE ABS(VariableA) = ABS(VariableB)



--
HTH,
Bob Barrows


From: --CELKO-- on 24 Jun 2010 11:01
>> I am trying to match a negative number to its exact opposite positive number, in my table ill use -1520.90 (Variable A) to match 1520.90 (Variable B) as the match I want. <<

SELECT ..
FROM ..
WHERE -a = b;

From: Peso on 28 Jun 2010 05:55

SELECT ..
FROM ..
WHERE a + b = 0

//Peter

 | 
Pages: 1
Prev: Compare Data
Next: I am trying to match a negative number to its exact opposite posit