exchange rate from seperate table

[DanHD]

Hello,

I need to find the right exchange rate from the rate table by a purchase document on the purchase date.
In the purchase table a have the docnr, date, and value in euro.
In the rate table I have for each date a currency and the rate,
for example:
5 march, USA, 0.75
12 april, USA, 0.81

So I need a query that’s give me for a PO doc on 5 march the follow results: the PO docnr(lines), PO date, PO value in euro and the rate of 0.75.

Because there are a lot of order lines I don’t want a where statement on the PO doc, but a list with all the PO nr’s and the right exchange rate.


Dan

 

[markros]

Well, I think it will be

select PO.*, R.Rate
from PO
OUTER APPLY (select top (1) * from Rates R
where R.Date <= PO.Date ORDER BY [Date] DESC) R

PluralSight Learning Library

 

[DanHD]

hi markros

thanks for you reply, but I'm afraid I asked my question in the wrong way.
I need the latest rate from the rate table. For each day and each currency (USA, GBP, etc) there is an entry in the table.


Dan

 

[markros]

To get the latest record per Currency in the Rates table we can use the following query:

;with cteRates as (select *, row_number() over (partition by Currency order by [Date] DESC) as Rn from Rates)

select * from cteRates where Rn = 1

Now, if you explain your problem a bit better as what you want, we can get the desired result.

PluralSight Learning Library