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 - 2002-04-01 : 09:17:28
|
| Luba writes "I hope that someone can help me with this query, other then using a temp table, and updating it by using a cursor I don't see how it can be done. Any suggestions are much appreciated!I have two tables1. Currency table which contains the currency name.CurrencyID, Name (i.e. 1 - CAD, 2 - USD, 3 - GBP, etc.)2. Log table which contains a log of the exchange rates against the canadian currency.LogID, LogCurrencyID, Date, Rate(i.e. 1 - 1 - 01/01/2002 - 1, 2 - 2 - 01/01/2002 - 0.7, 3 - 2 - 03/03/2002 - 0.6, 4 - 2 - 05/05/2002 - 0.5) This means that on Jan 1st the exchange rate for the Canadian dollar was 1, and for the US dollar it was 0.7. Then on March 3rd the exchange for the US dollar was changed to 0.6 of the canadian dollar. It also means that the US exchange rate will change to 0.5 on May 5th, but this rate is not active yet.I need a query to pull the logid, currencyid, currency name, date, and current rate. The part I am having problems with is the query should return the most recent active valid rate. This means the rate should have the date closest to today without going over. And of course, it should only return 1 record per currencyid.Thanx again!" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-01 : 09:54:05
|
| SELECT L.LogID, C.CurrencyID, C.Name, L.Date, L.RateFROM Currency C INNER JOIN LogTable L ON C.CurrencyID=L.LogCurrencyIDINNER JOIN (SELECT LogCurrencyID, Max(Date) MaxDate FROM LogCurrencyIDWHERE Date<=getdate() GROUP BY LogCurrencyID) MON L.LogCurrencyID=M.LogCurrencyID AND M.MaxDate=L.DateEdited by - robvolk on 04/01/2002 09:58:03 |
 |
|
|
Jay99
468 Posts |
Posted - 2002-04-01 : 09:57:47
|
| snipped . . .It is poor design to use reserved words for column and table names . . .Jay<O> |
|
|
|
|
|
|
|
|
date closest to today without going over