Sunday, February 21, 2010

Programmer - SQL join against date ranges?

Programmer Question

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?

No comments:

Post a Comment

LinkWithin

Related Posts with Thumbnails