Brilliant!

In really, really rare occasions Diddlbiker feels like he accomplished something brilliant. Today was one of those days. And the best thing is: my artwork is actually something small, elegant and comprehensible. What happened, was that my clients would need a table in a certain format, but the model it supported would demand a different format:

Client Wishes Model Demands 2006 0% 2006 1.0000 2007 5% 2007 1.0500 2008 4% 2008 1.0920 2009 3% 2009 1.1248 2010 2% 2010 1.1473 2011 1% 2011 1.1587

Clearly, my users want to specify a percentage increase for each year. I on the other hand, want a factor that compounds the increases year after year. The solution I came up with was non-equi self join with a kick: SELECT tblYear.Year, Exp(Sum(Log([tblRate].[Rate] + 1.0))) AS Factor FROM tblIncrease AS tblYear INNER JOIN tblIncrease AS tblRate ON tblRate.Year <= tblYear.Year GROUP BY tblYear.Year; I achieve two things in the query. First of all, by using a JOIN ... ON ... <= ... I'm able to pick up all years up to and including the 'current' year. Second, SQL doesn't have a PRODUCT aggregate function. By turning the factors into logarithms I can multiply them by adding them together - and SUM is something that you can do in SQL! Once they're all summed, I can reverse the logarithm by using an EXP (exponent) function. The query basically turns the first table (input-friendly) into the second table (model-friendly) without any scripts, temp tables or other garbage. Brilliant!

Speaking of brilliant: I saw a link on Joel on Software today about Elastic Tabstops - I wished every editor was that smart!