# Route Cost Matching in SQL

#### Steps to solve route cost matching in SQL.

Posted in Code on June 21, 2017  – 1 min read

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->BB->AB->CC->BC->DD->CActual
3000810048
0080101018

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:

1. To filter out those that fulfill sum(6 cols) equals to `Actual`
2. 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