I've a master table for exchange rates, which has FromDate, ToDate, MaterialGroup and Rate. The way it is stored is as follows:
1/1/2012, 30/6/2012, 1120, 4.5567
1/1/2012, 30/6/2012, , 4.5329
and the transaction table contains MaterialGroup to be joined to this table with the date range. The trick is that wherever the MaterialGroup doesn't join, it has to pick up the one with blank (line no 2 exchange rate). So for example, if the trx has happened on 20 Feb 2012 and MaterialGroup is 1156 for instance, the rate picked up would be 4.5329 whereas if it was for MaterialGroup 1120, the rate should be 4.5567.
Can you please help in getting me to achieve this ...