Post reply

Joining two tables - matching date or previous date before that

  • May 2, 2012 at 4:41 am

    #96838

    I have two tables, one with a lot of cash flows and one containing foreign exchange prices for a number of currencies for almost all dates. I would like to be extract the cash flows in connection with the appropriate Rate for each cash flow. A simple inner join is not an option as there might be a cash flow on a date where the CCYrates table does not have a rate for that day, and in such case it should instead use the latest rate prior to the cash flow date. The CCYrates can only have on rate per currency per day, so no need to worry about doublets for one currency for one date. Running on MS SQL-server. The flow table has about 75k records and the CCY rates has about 36k records.

    Cash flows

    ID

    Date

    Currency char(3)

    …….

    CCYrates

    ID

    Date

    Currency char(3)

    Rate

    Option 1 is to make a script that always makes sure that the CCYrates table is complete and any missing data on dates is copied from previous date. Data is normally imported in the morning, and a such script could be run automatically after the import. This solution is an option but it is not the prettiest as it creates more records in CCYrates that are needed as several records in a row will have same rate but only different rate.

    I am looking for the pretty way of doing this when data is pulled from the database?

    Is there a nice way to this with a join or would it be better to build a quick temp table for the range needed filling any blanks temporarily before running select and then joining the Cash Flows table to the temp table?

  • May 2, 2012 at 4:47 am

    #1482272

    Lars, which version of SQL Server are you using?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • May 2, 2012 at 4:50 am

    #1482273

    SQL Server 2008

  • May 2, 2012 at 4:55 am

    #1482274

    SELECT

    tt.*,

    x.ExchangeRate

    FROM TransactionTable tt

    CROSS APPLY (

    SELECT TOP 1 ExchangeRate

    FROM RatesTable rt

    WHERE rt.CurrencyPair = tt.CurrencyPair

    AND rt.ValidFrom <= tt.TransactionDate

    ORDER BY rt.ValidFrom DESC

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • May 2, 2012 at 5:06 am

    #1482280

    Impressed!

    Thanks a lot...

  • May 2, 2012 at 5:11 am

    #1482283

    Lars Jensen (5/2/2012)

    Impressed!

    Thanks a lot...

    Anytime 🙂

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

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