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:

1 2 3 4 5 6 7 8

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