Tricky 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)

Tricky join...

Author

Topic

nosenseofhumor1
Starting Member

7 Posts

Posted - 2010-03-23 : 17:05:53

hello,
i have two tables, one is salesLine, the other is exchangeRateHist.
from salesLine, i want to retrieve salesDate, salesNumber, itemNumber, itemCurrency, itemQuantity, itemPrice

from exchangeRateHist, i want to retrieve the most recent exchangeRate for currency = itemcurrency which had been applied prior to the salesLine record's creation.

to put it in broken psudeosql:


select salesLine.salesDate, salesLine.salesNumber, salesLine.itemNumber, salesLine.itemCurrency, salesLine.itemQuantity, salesLine.itemPrice, exchangeRateHist.exchangeRate from salesLine inner join (select currency, Max(exchangeRateDate), exchangeRate from exchangeRateHist where exchangerateDate < salesLine.salesDate) as RATE on RATE.currency = salesline.itemCurrency

please help!

nosenseofhumor1
Starting Member

7 Posts

Posted - 2010-03-23 : 17:07:02

can this only be done with a cursory function?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-23 : 18:44:02

Try this:

select salesLine.salesDate, salesLine.salesNumber, salesLine.itemNumber, salesLine.itemCurrency, salesLine.itemQuantity, 
		salesLine.itemPrice, RATE.exchangeRate 
from salesLine 
inner join (	select currency, Max(exchangeRateDate), exchangeRate 
				from exchangeRateHist 
				where exchangerateDate < salesLine.salesDate
					and currency = salesline.itemCurrency) as RATE 
	on RATE.currency = salesline.itemCurrency

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 00:47:55

quote:

Originally posted by DBA in the making

Try this:

select salesLine.salesDate, salesLine.salesNumber, salesLine.itemNumber, salesLine.itemCurrency, salesLine.itemQuantity, 
		salesLine.itemPrice, RATE.exchangeRate 
from salesLine 
inner join (	select currency, Max(exchangeRateDate), exchangeRate 
				from exchangeRateHist 
				where exchangerateDate < salesLine.salesDate
					and currency = salesline.itemCurrency) as RATE 
	on RATE.currency = salesline.itemCurrency

There are 10 types of people in the world, those that understand binary, and those that don't.

it wont work. you're taking MAX without applying group by in derived table
and even if you apply group by on currency and exchangeRate then also you'll get multiple lines per salesLine in output as exchange rate will vary for different dates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 00:51:43

I think it should be


select salesLine.salesDate, salesLine.salesNumber, salesLine.itemNumber, salesLine.itemCurrency, salesLine.itemQuantity, 
		salesLine.itemPrice, RATE.exchangeRate 
from salesLine 
cross apply (	select currency, exchangeRate,
                ROW_NUMBER() OVER (PARTITION BY currency ORDER BY exchangeRateDate DESC) AS RowNo
		from exchangeRateHist 
		where exchangerateDate < salesLine.salesDate
		and currency = salesline.itemCurrency) as RATE 
WHERE RATE.RowNo=1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 06:47:35

quote:
Originally posted by visakh16
it wont work. you're taking MAX without applying group by in derived table
and even if you apply group by on currency and exchangeRate then also you'll get multiple lines per salesLine in output as exchange rate will vary for different dates

*Slaps forehead*
What was I thinking?

Anyhow, this reminds me of something I was taught very early on, when I first started working with database tables. I was lucky enough have a very intelligent mentor that taught me a thing or 6 about table design. He had a saying. "STEP 1: Get the table structure right. If you can't do that, then do something else for a living."

An example he gave was similar to this one. It was a basic order entry system, with a product table, and a product price history table. He said you could just store the price in the history table, and then query it based on the max date to get the current price, as well as query it with the date of an order to get the price for that order. But it's much smarter to store the current price in the product table, copy that into the order items table when a record is added, and let the product price history table do it's job, eg. keeping track of historical prices. So when a price changes, update the product table, and add a record into the history table. When an order is placed, copy the price from the product table into the order items table. This way, the only time the price history table is queried, is when you need to know a historical price. Sure, it uses a little more storage. But it makes querying the current price and the price of an order much simpler.

In this case, I'd have included a exchangeRate column in the salesLine table, and populated is with the current exchange rate as records are added.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources