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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ratio math calc in sql
- Thread starter jcf27
- Start date
- Thread starter
- #3
JohnDTampaBay
Programmer
Here's a simplistic approach, but you should be able to use it as a starting point. Decimal values are, by definition, base ten; I don't think you'll ever get 2/3 from .66667 using a mathematical approach. Here goes...
Starting with
.66667 1.5 .8
Use 10 as the common denominator. Multiply the decimal portion by 10.
6.6667 5 8
Take the integer value. This will be your numerator.
6 5 8
Your ratios are
6/10 5/10 8/10
Resolve to the lowest ratio.
If MOD(numerator,5)=0 --divisible by 5
numerator = numerator / 5
AND denominator = denominator / 5
ELSE If MOD(numerator,2)=0 --divisible by 2
numerator = numerator / 2
AND denominator = denominator / 2
3/5 1/2 4/5
If there was an integer portion, multiply it by the denominator and add that to the numerator.
3/5 3/2 4/5
Now my brain hurts. I need a Mountain Dew. Good luck!
--Angel![[rainbow]](/data/assets/smilies/rainbow.gif "[rainbow] [rainbow]")
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.
Starting with
.66667 1.5 .8
Use 10 as the common denominator. Multiply the decimal portion by 10.
6.6667 5 8
Take the integer value. This will be your numerator.
6 5 8
Your ratios are
6/10 5/10 8/10
Resolve to the lowest ratio.
If MOD(numerator,5)=0 --divisible by 5
numerator = numerator / 5
AND denominator = denominator / 5
ELSE If MOD(numerator,2)=0 --divisible by 2
numerator = numerator / 2
AND denominator = denominator / 2
3/5 1/2 4/5
If there was an integer portion, multiply it by the denominator and add that to the numerator.
3/5 3/2 4/5
Now my brain hurts. I need a Mountain Dew. Good luck!
--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.
You asked for a way using SQL. The table may be a few thousand rows, but it would work.
If your DBMS provides for user-defined functions, then you can write a function to do what you need. (But that is not using ANSI SQL).
If you can read Pascal, check out the posts in thread102-459699 for the sort of code that can do the translation.
If your DBMS provides for user-defined functions, then you can write a function to do what you need. (But that is not using ANSI SQL).
If you can read Pascal, check out the posts in thread102-459699 for the sort of code that can do the translation.
Probably the easiest way to do what you want is to use Farey Series to calculate the nearest fraction.
Farey Series approximations start with the two nearest integers to your input number. Let's take .66667 as an example. Your first two guesses to the right fraction are
0/1 and 1/1
Neither of these guesses is particularly close, so generate another guess by adding the numerators and denominators of your first guesses, and reducing (if necessary) to lowest terms. That gives you (0+1)/(1+1)=1/2. You now have the following three guesses.
0/1 1/2 1/1
Figure out which two of these fractions .66667 fits between and throw out the other. In this case 1/2 < .66667 < 1/1 so you throw out 0/1 and are left with
1/2 and 1/1
You still aren't particularly close, so you need another guess. Use the same process of adding numerators and denominators of 1/2 and 1/1 to get (1+1)/(2+1) = 2/3. Now your guesses are
1/2 2/3 1/1
Again determine which two fractions .66667 fits between and throw out the other. You are left with
2/3 and 1/1
Bingo! 2/3 equals .66667. More precisely, it is correct to five decimal places. You are finished.
Most standard texts on number theory discuss Farey Series. There are also references on the internet if you do a web search. They explain why the algorithm described above doesn't skip any possible fractions. Also, it is important to realize that your input is a decimal number. As a general rule, decimals won't be exactly equal to the fraction you are trying to find. That means you will have to determine your own exit condition. Just how close to you need to be to say you are close enough? That all depends on what you are trying to do.
Farey Series approximations start with the two nearest integers to your input number. Let's take .66667 as an example. Your first two guesses to the right fraction are
0/1 and 1/1
Neither of these guesses is particularly close, so generate another guess by adding the numerators and denominators of your first guesses, and reducing (if necessary) to lowest terms. That gives you (0+1)/(1+1)=1/2. You now have the following three guesses.
0/1 1/2 1/1
Figure out which two of these fractions .66667 fits between and throw out the other. In this case 1/2 < .66667 < 1/1 so you throw out 0/1 and are left with
1/2 and 1/1
You still aren't particularly close, so you need another guess. Use the same process of adding numerators and denominators of 1/2 and 1/1 to get (1+1)/(2+1) = 2/3. Now your guesses are
1/2 2/3 1/1
Again determine which two fractions .66667 fits between and throw out the other. You are left with
2/3 and 1/1
Bingo! 2/3 equals .66667. More precisely, it is correct to five decimal places. You are finished.
Most standard texts on number theory discuss Farey Series. There are also references on the internet if you do a web search. They explain why the algorithm described above doesn't skip any possible fractions. Also, it is important to realize that your input is a decimal number. As a general rule, decimals won't be exactly equal to the fraction you are trying to find. That means you will have to determine your own exit condition. Just how close to you need to be to say you are close enough? That all depends on what you are trying to do.
swampBoogie
Programmer
<quote>
If your DBMS provides for user-defined functions, then you can write a function to do what you need. (But that is not using ANSI SQL).
</quote>
User defined function is part of ANSI SQL. It is supported by some vendors.
If your DBMS provides for user-defined functions, then you can write a function to do what you need. (But that is not using ANSI SQL).
</quote>
User defined function is part of ANSI SQL. It is supported by some vendors.
swampBoogie, I'm not sure how your post will help jcf27 solve the problem as stated in the original post, but since you seem to know a lot about it, perhaps you can point to a site that describes the published standard for user-defined functions.
I took a look at the first link listed in faq220-1073 (
) but I was unable to find anything pertaining to user-defined functions there. Can you elaborate, please?
I took a look at the first link listed in faq220-1073 (
) but I was unable to find anything pertaining to user-defined functions there. Can you elaborate, please?
swampBoogie
Programmer
No, this is a side issue. 
The first spefication for stored procedures and functions was published in 1996 so it is not included in SQL2. The standard is commonly called SQL/PSM. PSM stands for Persistant Stored Modules.
When SQL99 was published PSM was incorporated as a part of this standard.
If you look at the FAQ you mentioned there is a link to the draft for the sql 2003 draft standard. If you download that zip-file you will find the PSM specification as a PDF-file.
The first spefication for stored procedures and functions was published in 1996 so it is not included in SQL2. The standard is commonly called SQL/PSM. PSM stands for Persistant Stored Modules.
When SQL99 was published PSM was incorporated as a part of this standard.
If you look at the FAQ you mentioned there is a link to the draft for the sql 2003 draft standard. If you download that zip-file you will find the PSM specification as a PDF-file.
- Thread starter
- #10
- Status
