Subquery for CurrencyRate (1 Viewer)

[GUIDO22]

I store Sterling / currency exchange rates in my DB which apply between a date range (given as Start / End dates in the currency table : ie. an exchange rate 'period')
So for any one currency type (Euro/Swiss Franc etc.) - there can be many records in the EXCHANGE RATE table, each record covering a date range during which the actual exchange rate value applied).
I need a subquery to return the exchange rate that applies for the exchange rate 'period' during which a customer order was received (WeekIn).

EXCHANGE RATE Table : CurrencyId / Rate / StartDate / EndDate
CUSTOMERS Table : Id / Name / CurrencyId / WeekIn
ORDERS Table : CustomerId / Order / Qty / Price

I am able to return customer orders for a given year but I also need to calculate the Sterling equivalent prices for these orders using the exchange rate that was in place as governed by [WeekIn] date.

Any help gratefully received.
Thank you.

 

[arnelgp]

first query:

select customers.id, customers.name, customers.currencyid, customers.weekin, (select Rate from from exchangeT where customers.WeekIn Between StartDate and EndDate) As Rate from customers;

you save this query.
create new query from orders table and join it to the above query.

 

[GUIDO22]

first query:

select customers.id, customers.name, customers.currencyid, customers.weekin, (select Rate from from exchangeT where customers.WeekIn Between StartDate and EndDate) As Rate from customers;

you save this query.
create new query from orders table and join it to the above query.

Apologies... [WeekIn] is not in the CUSTOMERS table, it is in the ORDERS table.... my bad...

 

[GUIDO22]

Thanks... based on your subquery.. I have managed to return as required.. Nice one!