Most of us have gleefully used the GROUP BY function to perform summary operations such as SUM, AVERAGE, MAX and MIN on sets of values. A typical example of this would be the following where the ETL developer needs to determine the sum of each Group’s ItemValue column.
Using a simple T-SQL query, we can find the answer:
However, how can determine the factor of a set of numbers? This was the challenge I faced with an ETL project where the ultimate value was determined by a set of 1 to many adjustments. For example, a payment due of $100 was to be adjusted to 90% of the original value. That adjusted value would be adjusted again to 95% of the adjusted value. Mathematically this could be expressed as:
$100 x 90% = $90 $90 x 95% = $85.50
This can be expressed as a factor of the two adjustments:
$100 x (90% x 95%) = $85.50
Transact-SQL provides the following aggregate functions. Note there is no Factor fuction!!
The answer is to use natural log function LOG() and then the exponential EXP() function! Here is a table similar to the one shown above:
Here, using Excel, I have provided an example of the desired results.
Finally, here is how you would perform the same action using T-SQL.
Note that in SQL Server Integration Services, the expression function equivalents for natural log and exponential are LN() and EXP().