hi I am looking for a formula that solves the following problem. I
have two variables, one is Y which is the percentage increase by every
additional worker compared to the worker before. The second variable X
is how many worker are working on a job. I am looking for a formula
which gives me the total of work done.

So if we have Y = 0.5 and Y=1 the result should be 1.
if we have Y = 0.5 and Y=2 the result should be 1.5.
if we have Y = 0.5 and Y=3 the result should be 1.75
if we have Y = 0.5 and Y=4 the result should be 1.875
if we have Y = 0.8 and Y=2 the result should be 1.8
etc...
I am looking for a formula that can produce the result, WITHOUT using
the sum function. I need a non iterative function (meaning no sum from
... to.. ) that can be implemented into sql.

Hi bnc_user-ga,

Let's assume that there is an exponentiation operator ^, such that,
for example, 3 ^ 2 = 9 and 2 ^ 3 = 8.

The formula then is:

result = (1 - (Y ^ X)) / (1 - Y)

Example:

Let X = 4 and Y = 0.5.

(1 - (Y ^ X)) / (1 - Y) = (1 - (0.5 ^ 4)) / (1 - 0.5)
                        = (1 - 0.0625) / 0.5
                        = 0.9375 / 0.5
                        = 1.875

The only problem with this is that there is no such exponentiation
operator in standard SQL.  (At least there is none in SQL-92.  I was
unable to confirm this for SQL-99.)  However, this should not be a
serious problem, since it is very common for SQL implementations to
provide either an operator or a function that does exponentiation.  If
it's not a ^ operator in your SQL implementation, it may be a **
operator or a POWER function.  All you have to do is substitute
whatever your SQL implementation uses in the expression (Y ^ X).

This kind of formula is called a "geometric series."  For a reference
on this from Eric Weisstein's World of Mathematics, see:

http://mathworld.wolfram.com/GeometricSeries.html


Search Strategy:

I didn't remember that this was a geometric series, but it looked like
something for which there should be a standard formula and I knew it
was a series, so I searched for "power series" on Google.  This turned
out to be the wrong series, but the search led me to the World of
Mathematics web site, where I went from Power Series to Series (index)
to General Series to Series (article):

http://mathworld.wolfram.com/Series.html

That page said "If each term equals the previous multiplied by a
constant, it is said to be a geometric series," which I recognized as
your problem.  From there, I went to the geometric series page cited
above and got the formula.  I had to adjust it slightly to match the
problem as you posed it.

For information about SQL operators, I referred to a book:

SQL:  The Complete Reference by James R. Groff and Paul N. Weinberg
(Osborne/McGraw-Hill, 1999)


I hope this information is helpful.  If you need further explanation
or help with finding the exponentiation operator for your dialect of
SQL, please ask for a clarification.

--efn-ga

Excellent work!!! Thank you also for the SQL comments