我不知道你的表的结构,但从我的理解,你可以做这样的事情:
select pkInvoice, InvoiceAmount, ExchangeRate from Invoices i1 left outer join (select pkInvoice, max(EffectiveDate) as effDate from Invoices i2 inner join ExchangeRates ex2 on i.InvoiceDate > ex.EffectiveDate group by pkInvoice) rates on i1.pkInvoice = rates.pkInvoice left outer join ExchangeRates ex1 on rates.effDate = ex1.EffectiveDate
在这里,基本上,您为每个发票行获取具有最新生效日期但早于发票日期的汇率的生效日期。
然后将该生效日期与交换表一起加入以获取费率,并将该信息与您的发票表相结合......