Yesterday, I wrote about exception reporting. Recently we received two requests for exception reports in the same day. Here are two examples:
#1 Indicate when the quantity of line item voids within a single transaction exceeds a threshold
#2 Indicate number of cashiers whose count of voids in a single day exceeded a threshold
One of these came from a restaurant and the other from a hard goods retailer. Can anyone tell which came from which?
It is interesting to see how such different businesses still have the same fundamental requirements for exception reporting. Lets look at what #2 requires in detail.
First, you must have all the transactions captured for the day. Then you must count the number of voids that each cashier had each day. (If this data was in a SQL database, you might use a query like “SELECT count(item) FROM transaction_table WHERE day = “day you care about” GROUP BY cashier_id;”). That’s not too hard. But, now let’s assume you want that information aggregated by store region and the threshold should be one standard deviation from the mean void count of that region, for the same day part, over the last month! Anyone want to suggest the SQL for that?
This quickly can get to be a pretty daunting task for a video system. Frankly, at Envysion, we’re still trying to decide how much exception reporting is enough and/or when to partner with a company that performs exception reporting as its core business. We have some interesting ways we can make video seamlessly available to a 3rd party exception reporting system. I’ll talk about those mechanisms next week.