From: CELKO on 14 Jan 2010 20:37 Here is a version of the aggregate product function in SQL. You will need to have the logarithm and exponential functions. The idea is that there are three special cases  all positive numbers, one or more zeros, and some negative numbers in the set. You can find out what your situation is with a quick test on the sign () of the minimum value in the set. Within the case where you have negative numbers, there are two sub cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result. SELECT CASE MIN (SIGN(nbr)) WHEN 1 THEN EXP(SUM(LN(nbr)))  all positive numbers WHEN 0 THEN 0.00  some zeroes WHEN 1  some negative numbers THEN (EXP(SUM(LN(ABS(nbr)))) * (CASE WHEN MOD (SUM(ABS(SIGN(nbr)1)/ 2)), 2) = 1 THEN 1.00 ELSE 1.00 END) ELSE NULL END AS big_pi FROM Foobar; Or another way; SELECT CASE MIN(ABS(SIGN(nbr))) WHEN 0 THEN 0.00  some zeroes ELSE  no zeroes EXP(SUM(LOG(ABS(NULLIF(nbr, 0))))) * CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)1)/2) AS INTEGER), 2) = 1 THEN 1.00 ELSE 1.00 END END AS big_pi As an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. A. Melzak (WileyInterscience, 1983, ISBN 047186854X), is short mathematical book on the general principle of conjugacy. This is the method of using a transform and its inverse to reduce the complexity of a calculation.
From: Tom Cooper on 14 Jan 2010 21:08 Once again, you are ignoring the fact that this is a Microsoft SQL Server usergroup. The correct function is LOG(). LN() is not a valid function in Microsoft SQL Server. Tom "CELKO" <jcelko212(a)earthlink.net> wrote in message news:bcdeb27741a44cf4be7385fc01a00c87(a)m26g2000yqb.googlegroups.com... > Here is a version of the aggregate product function in SQL. You will > need to have the logarithm and exponential functions. The idea is that > there are three special cases  all positive numbers, one or more > zeros, and some negative numbers in the set. > > You can find out what your situation is with a quick test on the sign > () of the minimum value in the set. > > Within the case where you have negative numbers, there are two sub > cases: (1) an even number of negatives or (2) an odd number of > negatives. You then need to apply some High School algebra to > determine the sign of the final result. > > SELECT CASE MIN (SIGN(nbr)) > WHEN 1 THEN EXP(SUM(LN(nbr)))  all positive numbers > WHEN 0 THEN 0.00  some zeroes > WHEN 1  some negative numbers > THEN (EXP(SUM(LN(ABS(nbr)))) > * (CASE WHEN > MOD (SUM(ABS(SIGN(nbr)1)/ 2)), 2) = 1 > THEN 1.00 ELSE 1.00 END) > ELSE NULL END AS big_pi > FROM Foobar; > > Or another way; > > SELECT CASE MIN(ABS(SIGN(nbr))) > WHEN 0 THEN 0.00  some zeroes > ELSE  no zeroes > EXP(SUM(LOG(ABS(NULLIF(nbr, 0))))) > * CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)1)/2) AS INTEGER), > 2) = 1 > THEN 1.00 ELSE 1.00 END > END AS big_pi > > As an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. > A. Melzak (WileyInterscience, 1983, ISBN 047186854X), is short > mathematical book on the general principle of conjugacy. This is the > method of using a transform and its inverse to reduce the complexity > of a calculation.
First

Prev

Pages: 1 2 3 Prev: sql commands for datediff Next: Diferent times for execution the same code 