Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need math syntax :-)

Status
Not open for further replies.

katbear

Programmer
Mar 14, 2007
270
US
Hi,

I need some SQL syntax to do the following:

Compare 2 integer values (from 2 different tables) and make sure that the values DO NOT DIFFER by more than 10%.

Not sure how to write this!

Thanks
 
This will give you the percentage difference (absolute value):

select abs(((1.00 * int1) - int2)/int1)

The 1.00 * is a quick way to cast to a decimal (otherwise you'll get only integer values).

You could use a CASE expression or where clause to handle whether this is > or <= 10% , without knowign what you plan to do its' hard for me to make recommendations.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom
 
Code:
select table1.val1, table2.val2, 
   (table1.val1-table2.val2) as mydiff, 
   case (table1.val1-table2.val2)/table1.val1 when < .1 
     then 'Yes' else 'No' end
from table1
inner join table2 on table1.key1 = table2.key2
This is off the cuff and not tested.
Hope this gets you started.
djj
 
example

Code:
declare @a int,@b int
select @a =120,@b=130

--select (@a/(@b*1.00)) * 100
--select (@b/(@a*1.00)) * 100


select (@a/(@b*1.00)) * 100,(@b/(@a*1.00)) * 100,case when ((@a/(@b*1.00)) * 100) < 90 or  ((@a/(@b*1.00)) * 100) > 110 then 'out of range'
else 'in range' end



select @a =120,@b=140

select (@a/(@b*1.00)) * 100,(@b/(@a*1.00)) * 100,case when ((@a/(@b*1.00)) * 100) < 90 or  ((@a/(@b*1.00)) * 100) > 110 then 'out of range'
else 'in range' end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
I think you need to define how the percent difference should be calculated.

Usually, the formula is...

(Val1 - Val2) / Val[!]1[/!]

Or....

(Val1 - Val2) / Val[!]2[/!]

For example....

Code:
Declare @T1 Table(Id Int, Value Int)
Declare @T2 Table(Id Int, Value Int)

Insert Into @T1 Values(1, 10)
Insert Into @T2 Values(1,9)

Insert Into @T1 Values(2,9)
Insert Into @T2 Values(2,10)


Select T1.Id,
       T1.Value,
       T2.Value,
       100 * Abs(Convert(Numeric(10,2), (T1.Value - T2.Value))) / T1.Value As PercentDifference
from   @T1 T1 
       Inner Join @T2 T2 
         On T1.Id = T2.Id

Notice I am playing with the values 9 and 10.

(9-10)/9 = 0.111111
(10-9)/10 = 0.1



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
George,

Now I'm confused... which is it?

it seems that (Val1 - Val2) / Val1 is the only correct way, otherwise you get a wrong answer.

 
Well, 9 differs from 10 by 1/10th of 10, and 10 differs from 9 by .11111 of 9.

I think George is right. It depends on which value you employ as the comparator.
 
Usually, when you are talking about percentages, it is in reference to something. Example: How much has my house increased in value? I bought it for $1,000. It is now worth $1050.

The formula would be (1050-1000)/1000

In this example, "How much has my house increased in value." implies that you want to know, based on the original sale price, how much has it increased.

If you have nothing to base your decision on, then you should allow for both (which is effectively what ESquared meant with his post).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
The question is 10% of what? The smaller number or the larger number? The answer is different depending on how you look at it.

(Val1 - Val2) / Val1
or
(Val2 - Val1) / Val2

I buy a hamburger for $1. The merchant adds 10 cents sales tax. Was I charged 10% sales tax? Yes.

But what percent of the actual transaction did the government take? 9.09 %! (0.1 / 1.1)

So are 1 and 1.1 a 10% difference or is it a 9.09090909...% difference?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]
 
ESquared,

You explained that a lot better than I did. Thanks. And... I feel weird asking this... "Would you like cheese with that?".


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Hmmm, confusing... using the (val1 - val2/ val1) formula:

val1 = 10
val2 = 9

gives you 10% (which seems right)

val1 = 9
val2 = 10

gives you 11% (which seems really wrong!)

How can one avoid this complication? I suppose if you *always* use the larger value as Val1, you can avoid this.




 
Given 1.0 and 1.1:

The larger value is 10% more than the smaller.
The smaller value is 9.09% less than the larger.

Both are true.

Which do you want to measure?

1 * 1.1 * 0.9 [&ne;] 1
1 * 11/10 * 9/10 [&ne;] 1

1 * 11/10 * 10/11 = 1!
1 * 1.1 * 0.90909... = 1!

11/10 = 1.1
10/11 = 0.90909090...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]
 
George said:
"Would you like cheese with that?".
The best part about anything that has cheese is the cheese. So what do you think?!?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]
 
>> how much percent cheese does a cheese burger have?

Never enough!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top