Route Cost Matching in SQL
Steps to solve route cost matching in SQL.
Posted in Code on June 21, 2017 – 1 min read
Duration
2 hours
Problem
A fellow intern invited me to solve this problem.
They need to find the difference between the actual and expected cost of an item, and if the difference is non-zero, they need to attribute it to a category.
Log
To illustrate, consider the following table.
| A->B | B->A | B->C | C->B | C->D | D->C | Actual |
|---|---|---|---|---|---|---|
| 30 | 0 | 0 | 8 | 10 | 0 | 48 |
| 0 | 0 | 8 | 0 | 10 | 10 | 18 |
The first 6 columns represents expected cost of the routes taken and the 7th is the actual cost of going through these routes.
As you can see, each route is a pair, i.e. A->B and B->A. So there is a sub-total cost of the route between A and B, i.e. A->B + B->A
The objectives, then, are:
- To filter out those that fulfill sum(6 cols) equals to
Actual - To match the expected-actual absolute difference with one route cost
The solution is written in SQL:
WITH sum_route AS (
SELECT (AB+BA+BC+CB+CD+DC) AS value
FROM table
)
SELECT *
FROM table t, sum_route sum
WHERE t.actual<>sum
where t.actual<>sum is analogous to objective 1
