Edit Question - KnowledgeHub

Title Body

Consider two tables: Transactions , with amounts in a foreign currency: Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc. ExchangeRates , with the value of the primary currency (let's say dollars) in the foreign currency: Date Rate ========= ======= 2/1/2009 40.1 3/1/2009 41.0 4/1/2009 38.5 5/1/2009 42.7 etc. Exchange rates can be entered for arbitrary dates - the user could enter them on a daily basis, weekly basis, monthly basis, or at irregular intervals. In order to translate the foreign amounts to dollars, I need to respect these rules: A. If possible, use the most recent previous rate; so the transaction on 2/4/2009 uses the rate for 2/1/2009, and the transaction on 3/15/2009 uses the rate for 3/1/2009. B. If there isn't a rate defined for a previous date, use the earliest rate available. So the transaction on 1/2/2009 uses the rate for 2/1/2009, since there isn't an earlier rate defined. This works... Select t.Date, t.Amount, ConvertedAmount=( Select Top 1 t.Amount/ex.Rate From ExchangeRates ex Where t.Date > ex.Date Order by ex.Date desc ) From Transactions t ... but (1) it seems like a join would be more efficient & elegant, and (2) it doesn't deal with Rule B above. Is there an alternative to using the subquery to find the appropriate rate? And is there an elegant way to handle Rule B, without tying myself in knots?

Tags (comma-separated) Cancel