Most transactional systems have some kind of status field which is crucial to business operations and must be analyzed in a real-time manner. Here are some examples of transactional systems:
- Sales Orders
- Work Orders
- Ticket Status
- Technical Stock Analysis
- Case Management
- Lead Management
Executives often need comparative, relative or time-series data analysis in order to improve operations. They need to be able to drill into these figures in order to test a hypothesis or hunch about where inefficiency may lie. In a sales scenario, you’d want to know a close ratio. In support, we’d like to know how many tickets are open. In marketing, we’d be curious about which lead sources are delivering the highest percentage of qualified prospects.
The Magic Function
Unfortunately, there is no magic function in SQL that automatically separates the good stuff from the bad. The good news is that we can use some simple tricks to get the results we need.
Imagine that shipping costs are eating up too much of our profit and we need give management and operations staff the ability to detect and discover deliveries with high shipping costs.
One solution is to create views or computed columns that return a status code. Once in place, this may be analyzed in numerous ways. This view calculates a status that tells us if an order is late. The status is dynamic so the value will change depending on the date when you use it. We will create a view that will argument out our Orders table with the extra column.
[sql]
CREATE VIEW OrdersAndStatus AS
SELECT *, CASE WHEN (DATEDIFF(Day, [ShippedDate], [RequiredDate])) < 0 THEN '1' ELSE 0 END AS Late
FROM Orders;
[/sql]
Now we can easily query this view. The following query tells us what percent of orders are late in each country so managers can focus on problem areas.
[sql]
<span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 13px; line-height: 19px; white-space: normal;">SELECT ShipCountry,SUM(Late) AS LateOrders,COUNT(OrderID) AS TotalOrders,</span></pre>
SUM(Late)/CAST(COUNT(OrderID) AS FLOAT) AS PercentLate
FROM OrdersAndStatus GROUP BY ShipCountry
<pre>[/sql]
The Results
This gives the following results:
