Need math syntax :-)

[katbear]

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

 

[AlexCuse]

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

 

[djj55]

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

 

[SQLDenis]

example

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

 

[gmmastros]

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

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

 

[ESquared]

WHERE
int1 > int2 * 1.1
OR int2 > int1 * 1.1

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

 

[katbear]

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.

 

[SQLDenis]

I think George made a typo, it should be

(Val1 - Val2) / Val1

Or....

(Val2 - Val1) / Val2

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[philhege]

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.

 

[SQLDenis]

I feel weird saying this to you but this is elementary school math. Maybe a refresher is needed?



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

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

 

[ESquared]

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]

 

[gmmastros]

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

 

[katbear]

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.

 

[katbear]

SQLDenis, this IS the refresher

 

[ESquared]

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]

 

[ESquared]

"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]

 

[SQLDenis]

The real question would be how much percent cheese does a cheese burger have?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

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

 

[SQLDenis]

when cheese is below a certain percentage can you still call it a cheese burger?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions