Updating database tables when values are missing.

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).

From stackoverflow

• 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
    )
  

  Node : What if the ->nearest<- RateDate would be > BillDate? I know that he didn't ask for that, just out of curiosity.

  Tomalak : The OP was asking for the "nearest <= RateDate".

  Node : I know. As I said just out of curiosity, how would you solv this?

  Tomalak : I modified my answer.

  Node : Ouh, thx! :) *upvote*

• 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

• ...

  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?

  Ken White : Why are you not letting the original poster decide where to award the votes? Why are you so impatient? Why is it your business at all? You didn't post one of the two answers. Some manners might keep you from getting downvotes for your attitude.