Hard T-SQL Left Join?

Question

I have an ExchangeRates table that has a countryid and an exchanger, something with this:

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

Please note that there may be one and the same country with a different rate based on the date ... for example, the Euro was above .7400 on 2/14/2011 and now is .7700 on 7/20/2011.

I have another position table for listing items based on country. In this table, each item has a date associated with it. The position date should use the appropriate date and country based on the exchange rate. Therefore, using the above data, if I had a position with the euro of the country 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 will work if I only have one item in the table, but imagine that I had position date 8/1/2011, then this condition (er.ExchangeRateDate <= erliLineItemDate) will return several rows, therefore my query will 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 problem with this left join ... is that the dates are <= the position date, so it returns a lot of records, I need to do it somehow, but I don't know how to do it.

LineItem tables have multiple records, and each record can 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 entries for ParentID (ExpenseReportID) = 1. So, I take these entries and join the ExchangeRate table, where the Country in my position table = country of the exchange rate table (this part is simple) BUT the second condition I need to make is this is:

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

But the problem here is that it will return a few lines from my exchange rate table, because the euro is displayed twice.

Answer 1

Maybe something is missing for me, but as I understand it, the "dumb" solution to your problem is to use the ROW_NUMBER function and an external filter with your query "return too many records" (this can also be done using CTE, but I prefer the syntax view 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 I hope this helps!

Answer 2

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

 ExchangeRateToDate 

A separate process can update the previous record when adding a new one.

Then you can simply query for LineItemDate> = ExhangeRateDate and <= ExchangeRateToDate

(handling the latter, presumably using null ExchangeRateToDate, as a special case).

Answer 3

I would create a table in memory by creating an ExchangeRate table using ExchangeRateDates From and To.
All that remains to be done after this joins this CTE in your query instead of your ExchangeRate table and adds the condition when the date is between 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 4

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

Answer 5

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

Note that you need to change this to your own schema, and filter out results that are closer, but in the future. I hope this helps, but if that is not enough, I am sure I can post a more detailed answer.

Answer 6

You can use this as a correlated subquery, which will give you a table with the latest exchange values ​​for a given date (indicated in the 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 7

If I do not understand what you want to do, you can use the external code 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