Learn SQL - SELECT Product(column)

I have on occasion been quoted as suggesting that SQL code with temp tables can indicate the author lacks strong SQL skills. Consequently, I have been challenged to write single SQL statements to replace multiple statements stuck together with temp tables. One of these occasions was with Ron Soukup over a bottle of whisky, but that story deserves its own blog.

On this occasion (sometime in 1999) I was challenged to write a query to get the product of a set of numbers. On another bottle of whiskey, as it happens. My friend wanted something like the SUM() function, but he wanted the product of a set of numbers rather than the sum. The business application was quite simple. His client, a financial organisation, needed to calculate compound interes for money on overnight deposit. Each day will have a different market interest rate.

This problem had an interesting solution using a couple of maths functions - Sum(), Exp() and Log(). Here is something like the SQL that I wrote for them.

select exp(sum(LOG(OvernightInterestRate+1))) as CompoundRate
from OvernightDeposits
where TheDate Between '1999-07-23' and '2009-06-18'

Now that's really quite easy, and is much more efficient than putting the data into a temp table and reprocessing it. Having said that, it would be quite useful (and I thought quite easy) to include a Product() function in SQL Server.

For real-time SQL, OLAP and Data Mining demonstrations, goto http://RichardLees.com.au/Sites/Demonstrations