Simon worked in a small shop that supported a sales system. One of the features of the system was that sales commissions were stored in the database. For the sake of simplicity, the sales commissions were stored as the multiplier factor needed to compute the total sale. For example, a 5% commission on $100 would be $5, so the factor would be 1.05 so you could just multiply: 100 * 1.05 -> 105.

Of course, when they needed a report that showed the percent commission for a given sale, they had to work backward from the multiplier to get the actual value.

One of the Finance Team bean counters complained that this report is getting the wrong commission rate from the database for one sale. Simon went to have a look. It's an unusual commission rate we don't often use, 7.5%. It displays correctly in the application, but is showing up as just over 1% in the report.

That didn't make much sense. All the other sales commission rates showed up just fine, and a number is just a number - it can't possibly matter how often a particular commission rate is used, surely? The report had been set up by the Finance person's predecessor's predecessor. The source for the commission column was:

If {commission_rate} = 1.2 
   Then 20 
   Else If {commission_rate} = 1.15 
           Then 15 
           Else If {commission_rate} = 1.0 
                   Then 0 
                   Else If {commission_rate} = 1.16 
                           Then 16 
                           Else If {commission_rate} = 1.125 
                                   Then 12.5 
                                   Else If {commission_rate} = 1.1 
                                           Then 10
                                           Else {commission_rate} 

While commission rates were stored in the database as the factor to multiply the base price by to get the price with commission included, this report wanted the commission rate displaying as a percentage, so they needed converting. Its author had known the conversion for the two commission rates used most of the time, and apparently added others over time.

Of course this meant that the fix was simple: Finance simply needed to add this 'new' commission rate to the end of the list, and all would be well. They were happy that the cause of the problem had been identified, and they knew how to fix it.

However, Simon couldn't leave it at that. For one, he could see a future of intermittent requests to help 'fix' the report every time Sales agreed to use a commission percentage that hadn't been used before. Can't the reporting software do arithmetic? Finance didn't know, so he boldly gave it a go, removing all of the Ifs and replacing the expression with just:

({commission_rate} - 1) * 100

It worked fine. As Simon moved on to the next project, he wondered whether the report's author hadn't spotted the pattern between the raw rates from the database and the desired percentages, or whether simple arithmetic was beyond the capabilities of a member of the Finance team.


Photo credit: Dave Dugdale / Foter / CC BY-SA