Link to the exchange rate table 1

[JeffMachine]

I have various sales and orders queries that I must now link to a currency table to convert to USD.

The currency table looks like this:

currency fromdate rate
USD 1900-01-01 78.44
USD 2006-08-31 77.839
USD 2006-09-30 79.001
USD 2006-10-30 78.802

SO, to be able to do the conversion I need to get the rate for the order/invoice date.

If the order/invoice date is before 8/31/2006 a rate of 78.44 is used.
If the order/invoice date is between 8/31/2006 and 9/29/2006 a rate of 77.839 is used.
If the order/invoice date is between 9/30/2006 and 10/29/2006 a rate of 79.001 is used. And so on.

How can I link to this table using the other table's transaction date to return the correct rate?

Thanks ....

 

[sabinUE]

; with cr
 AS(select 'USD' as currency,'1900-01-01' as fromdate, 78.44 as rate union all
	select 	'USD','2006-08-31',77.839 union all
	select 	'USD','2006-09-30', 79.001 union all
	select 	'USD','2006-10-30', 78.802)
,[order] AS
(select '2006-08-30' as [invoiceDate] union all
 select '2006-08-31'  union all
 select '2006-09-30')

--this is the code
select O.invoiceDate ,R.rate
from [order] O
	outer apply
	(select top 1 rate
		from [cr] as C
		where  C.fromdate<=O.invoiceDate
		order by fromdate desc) as R

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin