procedure in SQL
I have a table that keeps exchange rates for multiple currencies:
fromCur, toCur, rate, effectiveDate
USD BRL .1 12/2/04
USD BRL .11 1/1/05
USD CAN .5 11/3/04 ...
and so on for all combinations.
I need to get the most recent rate for a given fromCur, for _EACH_ toCur and I can't think of a way to do it with out a procedural approach that:
1. Gets all ToCur's for a given fromCur (usd)
2. For each toCur, select top 1 rate from <table> where fromCur = 'usd' and toCur = @toCur order by effDate Desc
IS there a t-SQL only way to do this?
Thank you for any input.