Search code examples
sqlt-sqlcomparecurrencyrate

TSQL - How to find out if a Users Currency rate is correct based on another currency rate for the same user

We currently have an issue where some rates for users have not been entered correctly and I have been tasked to find out which entries are incorrect, but unfortunately I am bit stuck.

The requirements are as follows;

  • If user only has a GBP charge out rate, then do nothing
  • If user has Charge out rate in GBP + USD then; GBP must = USD / 1.55 otherwise FALSE

The following is how I started it:

We have a table called TIMERATE where a users charge out rate is entered, with a new line added every time the rate changes. It is possible for the user to have a charge out rate in more than one currency.

Here is a cut of the data in the table for a single user;

tkinit  tkeffdate   tkrt01  tkrtcur
LAU 01/02/2014  170 GBP
LAU 01/08/2014  260 GBP
LAU 01/12/2014  130 GBP
LAU 01/08/2014  260 USD
LAU 01/12/2014  210 USD
LAU 01/02/2015  260 USD

To find out the latest rate for each currency:

SELECT
    TKINIT as Timekeeper,
    MAX (tkeffdate) as MaxEffectiveDate,
    tkrtcur as Currency,
    Cast (NULL as decimal (16,2)) as Rate
INTO
    #LatestRate
FROM
    TIMERATE
GROUP BY
    TKINIT, tkrtcur
ORDER BY
    TKINIT

Then I updated the Rate in the temp table

update 
    #LatestRate
Set 
    Rate = tkrt01
from 
    #LatestRate
JOIN 
    Timerate on TKINIT = Timekeeper 
        and tkrtcur = Currency 
        and tkeffdate = MaxEffectiveDate

So i now have the latest rate for each currency for each user, but I do not know how to manipulate the data to fit the requirements

Does anyone have any ideas? Have I gone about this the wrong way?

Solution

  • Rabbit's answer is the easiest way to compare if the conversion was done correctly. Going off of your initial steps, you can run this query on the #LatestRate table to give you the results. Pls ensure the comparison in the case statement is working correctly. I have not tested this code. 

    SELECT
    GBP.Timekeeper as Timekeeper,
    GBP.MaxEffectiveDate as EffectiveDate,
    GBP.Currency as GBP,
    GBP.Rate as GBPRate,
    USD.Currency as USD,
    USD.Rate as USDRate,
    case 
        when USD.Rate is null then 'GBP Only' 
        when round(USD.Rate/1.55,2)=round(GBP.Rate,2) then 'Correct Conversion'
        else 'FALSE'
    end as IsConversion
FROM #LatestRate GBP
INNER JOIN #LatestRate USD
ON GBP.Timekeeper=USD.Timekeeper
and GBP.MaxEffectiveDate=USD.MaxEffectiveDate
and GBP.Currency='GBP' and USD.Currency='USD'