Query by linking date to transaction

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 2000 Forums

Transact-SQL (2000)

Query by linking date to transaction

Author

Topic

squashjunkie
Starting Member

9 Posts

Posted - 2006-04-14 : 05:28:03

I have two tables, a transaction table with sales order line items, with the following (simplified) structure.
[docdate, product, unitprice, qty, currency]

The second table contains the relevant currency exchange rates entered by the accounts department.
[currency, ratedate, exch_rate]

Currencies are not entered everyday, but instead are entered as and when fluctutaion is great enough for the accts dept to enter a new one. What I want to do is query the two tables so that each sales order line item in the transaction table will have an exchange rate. If there is no exchange rate for that date of the transaction, then it would take the last entered exchange rate for that particular currency.
e.g. exchange rate table can look like
USD 01.01.2006 1.65
USD 05.01.2006 1.63

therefore, any sales with dates such as the following :
03.01.2006 XXYYZZ $10 100 USD (WOULD TAKE USD @ 1.65)
05.01.2006 XXYYZZ $10 50 USD (WOULD TAKE USD @ 1.63)

I'm a bit stuck on how to go forwards with this query...any help is greatly appreciated...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-14 : 09:28:51

If this doesn't work, post the DDL for both table (create table scripts) as well as some insert statements to populate some sample data and expected results:


select	t.docdate
	,t.product
	,t.unitprice
	,t.qty
	,t.currency
	,c.exch_rate
from	(
	select	t.docdate
		,max(c.ratedate) ratedate
	from	transactionTable t
	join	currencyTable c
			on c.currency = t.currency
			and c.ratedate <= t.docdate
	group by t.docdate
	) dts
join	transactionTable t
		on t.docdate = dts.docdate
join	currencyTable c
		on c.currency = t.currency
		and c.ratedate = dts.ratedate

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources