Tough T-SQL To Left Join?

Question

I've got a table of ExchangeRates that have a countryid and an exchangeratedate something to this effect:

ExchangeRateID   Country   ToUSD      ExchangeRateDate
1                  Euro     .7400     2/14/2011
2                  JAP      80.1900   2/14/2011
3                  Euro     .7700      7/20/2011

Notice there can be the same country with a different rate based on the date...so for instance above Euro was .7400 on 2/14/2011 and now is .7700 7/20/2011.

I have another table of line items to list items based on the country..in this table each line item has a date associated with it. The line item date should use the corresponding date and country based on the exchange rate. So using the above data if I had a line item with country Euro on 2/16/2011 it should use the euro value for 2/14/2011 and not the value for 7/20/2011 because of the date (condition er.ExchangeRateDate <= erli.LineItemDate). This would work if I only had one item in the table, but imagine I had a line item date of 8/1/2011 then that condition (er.ExchangeRateDate <= erliLineItemDate) would return multiple rows hence my query would fail...

SELECT     
    er.ExchangeRateID, 
    er.CountryID AS Expr1, 
    er.ExchangeRateDate, 
    er.ToUSD, 
    erli.ExpenseReportLineItemID, 
    erli.ExpenseReportID, 
    erli.LineItemDate
FROM         
    dbo.ExpenseReportLineItem AS erli 
LEFT JOIN
    dbo.ExchangeRate AS er 
ON er.CountryID = erli.CountryID 
AND DATEADD(d, DATEDIFF(d, 0, er.ExchangeRateDate), 0) <= DATEADD(d, DATEDIFF(d, 0, 
                      erli.LineItemDate), 0)
WHERE     (erli.ExpenseReportID = 196)

The issue with this left join...is because the dates are <= the line item date so it returns many records, I would have to somehow do this but dont know how.

The LineItem tables has multiple records and each record could have its own CountryID:

Item            Country      ParentID    LineItemDate
Line Item 1      Euro           1           2/14/2011
Line Item 2      US             1           2/14/2011
Line Item3       Euro           1           2/15/2011

So there are three records for ParentID (ExpenseReportID) = 1. So then I take those records and join the ExchangeRate table where the Country in my line item table = the country of the exchange rate table (that part is easy) BUT the second condition I have to do is the:

  AND DATEADD(d, DATEDIFF(d, 0, er.ExchangeRateDate), 0) <= DATEADD(d, DATEDIFF(d, 0, 
                          erli.LineItemDate), 0)

But here is where the issue is because that will return multiple rows from my exchange rate table because euro is listed twice.

Answer 1

I would create an in memory table creating an ExchangeRate table with ExchangeRateDates From & To.
All that's left to do after this is joining this CTE in your query instead of your ExchangeRate table and add a condition where the date is betweenthe date from/to.

SQL Statement

;WITH er AS (
    SELECT  rn = ROW_NUMBER() OVER (PARTITION BY er1.ExchangeRateID ORDER BY er2.ExchangeRateDate DESC)
            , er1.ExchangeRateID
            , er1.Country
            , ExchangeRateDateFrom = ISNULL(DATEADD(d, 1, er2.ExchangeRateDate), 0)
            , ExchangeRateDateTo = er1.ExchangeRateDate
            , er1.ToUSD
    FROM    @ExchangeRate er1
            LEFT OUTER JOIN @ExchangeRate er2
                ON  er1.Country = er2.Country
                    AND er1.ExchangeRateDate >= er2.ExchangeRateDate
                    AND er1.ExchangeRateID > er2.ExchangeRateID     
)
SELECT  er.ExchangeRateID, 
        er.CountryID AS Expr1, 
        er.ExchangeRateDateTo, 
        er.ToUSD, 
        erli.ExpenseReportLineItemID, 
        erli.ExpenseReportID, 
        erli.LineItemDate
FROM    dbo.ExpenseReportLineItem AS erli 
        LEFT JOIN er ON er.CountryID = erli.CountryID 
                        AND DATEADD(d, DATEDIFF(d, 0, er.ExchangeRateDateTo), 0) <= DATEADD(d, DATEDIFF(d, 0, erli.LineItemDate), 0)
                        AND DATEADD(d, DATEDIFF(d, 0, er.ExchangeRateDateFrom), 0) >= DATEADD(d, DATEDIFF(d, 0, erli.LineItemDate), 0)
WHERE   (erli.ExpenseReportID = 196)
        and er.rn = 1

Test script

DECLARE @ExchangeRate TABLE (
    ExchangeRateID INTEGER
    , Country VARCHAR(32)
    , ToUSD FLOAT
    , ExchangeRateDate DATETIME
)   

INSERT INTO @ExchangeRate 
VALUES  (1, 'Euro', 0.7400, '02/14/2011')
        , (2, 'JAP', 80.1900, '02/14/2011')
        , (3, 'Euro', 0.7700, '07/20/2011')     
        , (4, 'Euro', 0.7800, '07/25/2011')     

;WITH er AS (
    SELECT  rn = ROW_NUMBER() OVER (PARTITION BY er1.ExchangeRateID ORDER BY er2.ExchangeRateDate DESC)
            , er1.ExchangeRateID
            , er1.Country
            , ExchangeRateDateFrom = ISNULL(DATEADD(d, 1, er2.ExchangeRateDate), 0)
            , ExchangeRateDateTo = er1.ExchangeRateDate
            , ToUSD = er1.ToUSD
    FROM    @ExchangeRate er1
            LEFT OUTER JOIN @ExchangeRate er2
                ON  er1.Country = er2.Country
                    AND er1.ExchangeRateDate >= er2.ExchangeRateDate
                    AND er1.ExchangeRateID > er2.ExchangeRateID     
)
SELECT  *
FROM    er
WHERE   rn = 1

Answer 2

You can use this as an correlated subquery that will give you a table with the most recent exchange values for a given date (indicated in a comment):

SELECT * 
FROM er
    INNER JOIN
    (
        SELECT CountryID, MAX(ExchangeRateDate) AS ExchangeRateDate
        FROM er
        WHERE ExchangeRateDate <= '9/1/2011' 
            -- the above is the date you will need to correlate with the main query...
        GROUP BY Country
    ) iq
    ON iq.Country = er.Country AND er.ExchangeRateDate = iq.ExchangeRateDate

So the full query should look something like this:

SELECT     
    iq2.ExchangeRateID, 
    iq2.CountryID AS Expr1, 
    iq2.ExchangeRateDate, 
    iq2.ToUSD, 
    erli.ExpenseReportLineItemID, 
    erli.ExpenseReportID, 
    erli.LineItemDate
FROM dbo.ExpenseReportLineItem AS erli 
    LEFT JOIN
    (
        SELECT * 
        FROM ExchangeRate er
            INNER JOIN
            (
                SELECT CountryID, MAX(ExchangeRateDate) AS ExchangeRateDate
                FROM ExchangeRate er
                WHERE ExchangeRateDate <= erli.LineItemDate 
                -- the above is where the correlation occurs...
                GROUP BY Country
            ) iq
            ON iq.Country = er.Country AND er.ExchangeRateDate = iq.ExchangeRateDate
    ) iq2
    ON er.CountryID = erli.CountryID 
        AND DATEADD(d, DATEDIFF(d, 0, iq2.ExchangeRateDate), 0) <= DATEADD(d, DATEDIFF(d, 0, erli.LineItemDate), 0)
    WHERE     (erli.ExpenseReportID = 196)

Answer 3

I may be missing something here, but as I understand it the "dumb" solution to your problem is to use A ROW_NUMBER function and outer filter with your existing "returns too many entries" query (this can also be done with a CTE, but I prefer the derived table syntax for simple cases like this):

SELECT *
FROM (
    SELECT     
        er.ExchangeRateID, 
        er.CountryID AS Expr1, 
        er.ExchangeRateDate, 
        er.ToUSD, 
        erli.ExpenseReportLineItemID, 
        erli.ExpenseReportID, 
        erli.LineItemDate,
        ROW_NUMBER() OVER (PARTITION BY ExpenseReportID, ExpenseReportLineItemID ORDER BY ExchangeRateDate DESC) AS ExchangeRateOrderID
    FROM dbo.ExpenseReportLineItem AS erli 
    LEFT JOIN dbo.ExchangeRate AS er 
        ON er.CountryID = erli.CountryID 
            AND DATEADD(d, DATEDIFF(d, 0, er.ExchangeRateDate), 0) 
                <= DATEADD(d, DATEDIFF(d, 0, erli.LineItemDate), 0)
    WHERE (erli.ExpenseReportID = 196)
        --For reasonable performance, it would be VERY nice to put a filter
        -- on how far back the exchange rates can go here:
        --AND er.ExchangeRateDate > DateAdd(Day, -7, GetDate())
) As FullData
WHERE ExchangeRateOrderID = 1

Sorry if I misunderstood, otherwise hope this helps!

Answer 4

If i don't misunderstand what you want to do you could use an outer apply to get the latest exchange rate.

select *
from ExpenseReportLineItem erli
  outer apply (select top 1 *
               from ExchangeRates as er1
               where er1.Country = erli.Country and
                     er1.ExchangeRateDate <= erli.LineItemDate 
               order by er1.ExchangeRateDate desc) as er

Answer 5

This can be solved by using one or more CTEs. This earlier SO question should have the needed building blocks : How can you use SQL to return values for a specified date or closest date < specified date?

Note that you have to modify this to your own schema, and also filter out results that are closer but in the future. I hope this helps, but if not enough then I'm sure I can post a more detailed answer.

Answer 6

Perhaps you can try using a table expression to get to your TOP 1 and then JOIN to the table expression. Does that make sense? Hope this helps.

Answer 7

It would make your life a lot easier if you could add an additional column to your ExchangeRates table called (something like)

ExchangeRateToDate

A separate process could update the previous entry when a new one was added.

Then, you could just query for LineItemDate >= ExhangeRateDate and <= ExchangeRateToDate

(treating the last one, presumably with a null ExchangeRateToDate, as a special case).