stephenejones1960 (4/19/2014)
Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.
You'll have to check for accuracy because of the nature of FLOAT calculations but if you sum the LOG of numbers, the ANTILOG (which is EXP in SQL Server) will be the PRODUCT of the numbers.
For example,
WITH
cteSomeNumbers(N) AS
(
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
)
SELECT EXP(SUM(LOG(N)))
FROM cteSomeNumbers
;
The answer is 120.
--Jeff Moden
Change is inevitable... Change for the better is not.