Post reply

SQL Query to find the nearest date for Currency ExchangeRate

  • December 17, 2010 at 6:50 am

    #244490

    Hi

    I have a transaction table (Invoices) and a CurrencyTable. In Currency table Currency Exchnage Rates are available with Exchange Date.

    In transaction table there two columns (InvoicePrice, InvoiceCurrency).

    InvoicePrice store InvoiceAmt and InvoiceCurrency column store currency in which Invoice generated. I need to convert from InvoiceCurrency to USD (US Dollar) and for that I have use Currency Table.

    There are mulpitle Exchnage Rates are available in Currency Table with respective Exchange Date.

    I have to pick up the latest Exchnage Rate according to Invoice Date.

    For Example see below

    Currency Table

    ExchnageRate, ExchnageDate, FromCurr, ToCurr

    3.45, 1-Jan-2010, EUR, USD

    3.54, 15-Jan-2010, EUR, USD

    Invoices

    1st Transaction

    -----------------

    InvoiceDate, InvoiceCurrency, InvoiceAmt

    12-Jan-2010, EUR, 500,

    This Transaction should use the Exchnage Rate of Date 1-Jan-2010

    2nd Transaction

    ---------------------

    InvoiceDate, InvoiceCurrency InvoiceAmt,

    18-Jan-2010, EUR, 900,

    This Transaction should use the Exchnage Rate of Date 15-Jan-2010

    Can anyone suggest what SQL query I need write to achive above logic.

  • December 17, 2010 at 1:36 pm

    #1264234

    Can you post the table definitions so that we can give you a working query instead of pseudocode?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply