Updating database tables when values are missing.

Question

I have two tables :

Bill :

create table Bill(
               BillDate datetime,
               Amount decimal(10,2) ,
               BillCurrency varchar(3) ,
               ReportingCurrency decimal(10,2))

FxRate :

create table FxRate( 
               RateDate datetime,
               SourceCurrency varchar(3),
               TargetCurrency varchar(3),
               ExchangeRate decimal(15,4))

This is what I want to do :

I want to update my Bill table as

update Bill 
set ReportingCurrency = FxRate.ExchangeRate * Bill.Amount
from FxRate
where FxRate.RateDate = Bill.BillDate

In this update all the rows which have an entry for that particular date will get the new reportingcurrency data. Since Bill table can have multiple rows eligible for the update , I have the following problem :

For the rows where there was no entry in FxRate table (for that date), the ReportingCurrency becomes NULL. I want to go back to the nearest <= RateDate and pick up the exchange rate.Is that possible using modifications in the same update statement or some other efficient method? (I want to avoid a cursor).

Answer 1

Of course this is possible - as a SELECT this would be:

SELECT
  b.BillDate,
  r.RateDate,
  r.ExchangeRate
FROM
  Bill b
  LEFT JOIN FxRate r ON r.RateDate = (
    SELECT MAX(RateDate) FROM FxRate WHERE RateDate <= b.BillDate
  )

Therefore, as an UPDATE:

UPDATE
  Bill
SET
  ReportingCurrency = r.ExchangeRate * b.Amount
FROM
  Bill b
  LEFT JOIN FxRate r ON r.RateDate = (
    SELECT MAX(RateDate) FROM FxRate WHERE RateDate <= b.BillDate
  )

The solution makes these assumptions:

• there is never more than one FxRate record for any given day
• there is an FxRate record that predates all Bill records

If these assumptions are not applicable, the query must be accommodated.

Also note that this is SQL Server syntax. It's possible that Sybase is a little different.

---

Since a commenter showed interest in a general "nearest date" solution:

UPDATE
  Bill
SET
  ReportingCurrency = r.ExchangeRate * b.Amount
FROM
  Bill b
  LEFT JOIN FxRate r ON r.RateDate = (
    SELECT TOP 1 RateDate 
    FROM         FxRate
    ORDER BY     ABS(DATEDIFF(d, RateDate, b.BillDate)), RateDate
  )

This would be quite slow, though. Alternative:

UPDATE
  Bill
SET
  ReportingCurrency = CASE 
                      WHEN DATEDIFF(d, r1.RateDate, b.BillDate) <= DATEDIFF(d, b.BillDate, r2.RateDate)
                      THEN r1.ExchangeRate
                      ELSE COALESCE(r2.ExchangeRate, r1.ExchangeRate)
                      END * b.Amount
FROM
  Bill b
  LEFT JOIN FxRate r1 ON r1.RateDate = (
    SELECT MAX(RateDate) FROM FxRate WHERE RateDate <= b.BillDate
  )
  LEFT JOIN FxRate r2 ON r2.RateDate = (
    SELECT MIN(RateDate) FROM FxRate WHERE RateDate >= b.BillDate
  )

Answer 2

You should be able to achieve this with a subquery. Hopefully my example below will work unmodified (and be error free!) The only note is you need to substitute your reporting (base) currency for my assumed 'USD'.

UPDATE Bill SET ReportingCurrency = (Bill.Amount * 
  (SELECT TOP 1 FxRate.ExchangeRate FROM FxRate
   WHERE FxRate.SourceCurrency = Bill.BillCurrency
   AND FxRate.TargetCurrency = 'USD'
   AND FxRate.RateDate <= Bill.BillDate
   ORDER BY FxRate.RateDate DESC))

Hope this helps. EDIT - Added ORDER BY Clause

Answer 3

...

LEFT JOIN FxRate r ON b.BillDate = ( SELECT MAX(RateDate) FROM FxRate WHERE RateDate <= b.BillDate ) ...

when RateDate < BillDate => BillDate will NEVER be equal that. Right? Solution/Answer 2 is correct. Why not giving the points to it?