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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-04-19 : 10:20:32
|
| Jason writes "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 likeUSD 01.01.2006 1.65USD 05.01.2006 1.63therefore, 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." |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-19 : 15:21:44
|
| This may not be the best way but here is an option:select b.field, a.Ratefrom TransactionTable b inner join RatesTable A on a.ConvDate = (select max(ConvDate) from RateTable where type = b.type and ConvDate <= b.tran_date) and a.type = b.type(Where TYPE is the rate type USD etc.)Hope it helps,Dalton |
 |
|
|
|
|
|
|
|
Query with transactions and Exchange rates