Monday, June 15, 2009

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

4 comments:

Tony Bain said...

Excellent! A while back my typical day used to largely consist of re-writing large batches or cursor based batches in more efficient set based operations. I found this old link to a SQL Server Magazine competition for writing a cross tab in a single query.

Kristen Hodges said...

While I generally agree with you, I'm curious as to how then you would approach populating a nested set table?

Amit Bulbule said...

finding the product using sum,exp and log really works... thanks for the tip..

akhila athresh said...

Thanks a bunch for the tip :)