Help with a JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a JOIN

Author

Topic

Marmot74
Starting Member

4 Posts

Posted - 2007-02-01 : 17:46:23

I have the following query

SELECT SUM(Invoices.extended_price*ISNULL(EUR_Exchange_Rates.ExchangeRate,1))
FROM
dbo.tbl_ExchangeRate EUR_Exchange_Rates
JOIN tbl_rpt_Invoiced_Sales_Report Invoices
ON (EUR_Exchange_Rates.ToCurrencyCode='EUR'
AND EUR_Exchange_Rates.FromCurrencyCode=Invoices.Nat_Cur_Code
AND Invoices.Date_Applied BETWEEN EUR_Exchange_Rates.EffectiveDate
AND EUR_Exchange_Rates.ExpirationDate-1)

The problem is returns multiple records sometimes because in the EUR_Exchange_Rates I need to also only join against the latest expirationdate and adddate values. How do I make it so it only joins against the latest one?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-02 : 01:32:34

Give us sample data and your expected output based on that sample data.

Peter Larsson
Helsingborg, Sweden

cornetto
Starting Member

2 Posts

Posted - 2007-02-02 : 07:21:27

I don't understand the query. What does the last line:
AND EUR_Exchange_Rates.ExpirationDate-1)
do? Is it a check that there is an expiration date, or is it a typo?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-02 : 09:17:48

No, the last AND is the range limit for the previous BETWEEN.
Read complete query.

SELECT	SUM(Invoices.extended_price * ISNULL(EUR_Exchange_Rates.ExchangeRate, 1))
FROM		dbo.tbl_ExchangeRate EUR_Exchange_Rates 
JOIN		tbl_rpt_Invoiced_Sales_Report Invoices ON EUR_Exchange_Rates.ToCurrencyCode = 'EUR' 
			AND EUR_Exchange_Rates.FromCurrencyCode = Invoices.Nat_Cur_Code 
			AND Invoices.Date_Applied BETWEEN EUR_Exchange_Rates.EffectiveDate AND EUR_Exchange_Rates.ExpirationDate - 1

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources