Saturday, February 18, 2006

Spot the Bug

Here is a completely made up query, but is close to one that I wrote recently to spread an overhead cost value (@MarketingExpenses) over a list of invoices. This is just one step in a complex series of calculations that helps to determine profitability of products or services sold.

SELECT 
CASE
WHEN COALESCE(b.sum_of_sales,0) > 0
THEN (a.sales / b.sum_of_sales) * @MarketingExpenses
ELSE 0
END AS ShareOfMarketingExpenses
FROM
invoice a LEFT JOIN regional_sum b
ON a.region_code = b.region_code


This query assumes that there's a table called [regional_sum] that has the sum of sales for each region. The [invoice] table is joined to [regional_sum] by linking the "region_code" fields of both tables, and since a LEFT JOIN is used, it will include all rows from [invoice] and only those rows in [regional_sum] that matches (if there is not a match, then NULL values will be used for the [regional_sum] data).

Now, the point of the COALESCE function is to return the first non-NULL value in the supplied list of values (b.sum_of_sales and zero). In this case, I'm simply trying to handle NULL values by changing them into zeros.

You'll also notice that I'm using a SQL CASE statement. I do this because I need to prevent the dreaded "divide by zero" error, so I ensure that the "sum_of_sales" field is not zero before trying to calculate a percentage (otherwise, I return zero as the result of the calculation).

The percentage is then multiplied by the predetermined "@MarketingExpenses" value in order to give you a number of marketing dollars that could be attributed to that one invoice record ("ShareOfMarketingExpenses").

Let me just assure you that the query above compiles and works as written, so the bug is not a syntax error. However, there was an issue that came up as we were trying to reconcile data from the new system with old/validated numbers (the sum of all of the "ShareOfMarketingExpenses" values did not equal the specified "@MarketingExpenses" value).

I'll leave it as an exercise to the reader, but I'll also provide some sample data:

============
REGIONAL_SUM
============

region_code sum_of_sales
----------- ------------
a0001 12345.67
a0002 432312.33
b0001 -123.23
b0004 34322.23
b0005 -12.12
c0006 234328.23

(Negative sales are probably due to internal accounting of moving inventory around from one region to another, but I'm not that close to this [fictitious] business, so I can't explain the numbers any better).

=======
INVOICE
=======

customer_id region_code sales
----------- ----------- ----------
1 a0001 12.32
1 a0001 33.23
2 b0001 5.34
3 c0006 1232.21
3 b0004 322.22
4 a0003 232.23
[the list goes on, but pretend that the
sales for each region correctly sums up
to the values listed in the above table]

It was a pretty cool "Ah ha!" moment for me when I spotted the flaw. Your mileage may vary.