Databases
StackOverflow2013
Postsdbo
POTough T-SQL To Left Join?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTough T-SQL To Left Join?
    text
    copied!<p>I've got a table of ExchangeRates that have a countryid and an exchangeratedate something to this effect:</p> <pre><code>ExchangeRateID Country ToUSD ExchangeRateDate 1 Euro .7400 2/14/2011 2 JAP 80.1900 2/14/2011 3 Euro .7700 7/20/2011 </code></pre> <p>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.</p> <p>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 &lt;= 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 &lt;= erliLineItemDate) would return multiple rows hence my query would fail...</p> <pre><code>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) &lt;= DATEADD(d, DATEDIFF(d, 0, erli.LineItemDate), 0) WHERE (erli.ExpenseReportID = 196) </code></pre> <p>The issue with this left join...is because the dates are &lt;= the line item date so it returns many records, I would have to somehow do this but dont know how.</p> <p>The LineItem tables has multiple records and each record could have its own CountryID:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code> AND DATEADD(d, DATEDIFF(d, 0, er.ExchangeRateDate), 0) &lt;= DATEADD(d, DATEDIFF(d, 0, erli.LineItemDate), 0) </code></pre> <p>But here is where the issue is because that will return multiple rows from my exchange rate table because euro is listed twice.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload