SQL Server - Validation against duplicate values in reverse in two columns [Example Inside]

Question

I am trying to implement validation to check whether the same FX rate value has been entered in reverse order. Users would populate a spreadsheet consisting of three columns:

• From Currency
• To Currency
• Rate

The user would add data into a spreadsheet to import through the system, below is a mock example of how the data would look:

I have the structure of the validation in place, I am just wondering how I would write a select statement in order to return all rows which have both directions (like the first two rows in this example), due to them being the same currency, just in reverse order.

Answer 1

Hmmm, you could use:

select fx.*
from fx
where exists (select 1
              from fx fx2
              where fx2.fromc = fx.toc and fx2.toc = tx.fromc and fx2.rate = fx.rate
             );

Actually, you might extend this check to be sure the values come close to being arithmetic inverses of each other. Because there is some slack with decimal values, I would suggest:

select fx.*
from fx
where exists (select 1
              from fx fx2
              where fx2.fromc = fx.toc and fx2.toc = tx.fromc and
                    abs(fx2.rate * fx.rate - 1) > 0.001
             );

This checks that the product is off by more than one thousandth. The exact threshold you want depends on your needs.