考虑两个表：
交易金额为外币：
Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc.
ExchangeRates,以外币的主要货币(让我们说美元)的价值：
Date Rate ========= ======= 2/1/2009 40.1 3/1/2009 41.0 4/1/2009 38.5 5/1/2009 42.7 etc.
可以在任意日期输入汇率 – 用户可以每天,每周,每月或不定期输入.
为了将外国金额翻译成美元,我需要遵守这些规则：
A.如果可能,使用最新的上一个速率;所以2009年2月4日的交易使用率为2009年2月1日,而2009年3月15日的交易使用率为2009年3月1日.
B.如果没有为之前的日期定义费率,请使用可用的最早费率.所以2009年1/2月的交易使用率为2009年2月1日,因为没有定义较早的利率.
这样做…
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
…但(1)似乎加入会更有效率优雅,(2)它不涉及上述规则B.
有没有办法使用子查询找到合适的比率？有没有一个优雅的方式来处理规则B,而不是束缚自己？
解决方法
您可以先按日期排序的汇率进行自我加入,以便您拥有每个汇率的开始和结束日期,而不会在日期中发生任何重叠或间隔(可能会将其视为数据库的视图)在我的情况下,我只是使用一个普通的表格表达式).
现在加入这些“准备”率的交易是简单有效的.
就像是：
WITH IndexedExchangeRates AS (
SELECT Row_Number() OVER (ORDER BY Date) ix,Date,Rate
FROM ExchangeRates
),RangedExchangeRates AS (
SELECT CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime)
ELSE IER.Date
END DateFrom,COALESCE(IER2.Date,GETDATE()) DateTo,IER.Rate
FROM IndexedExchangeRates IER
LEFT JOIN IndexedExchangeRates IER2
ON IER.ix = IER2.ix-1
)
SELECT T.Date,T.Amount,RER.Rate,T.Amount/RER.Rate ConvertedAmount
FROM Transactions T
LEFT JOIN RangedExchangeRates RER
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo)
笔记：
>你可以在远期的日期替换GETDATE(),我假设没有将来的费率是已知的.>规则(B)是通过将第一个已知汇率的日期设置为sql Server datetime支持的最小日期来实现的,该日期应该是(根据定义,如果它是用于Date列的类型)最小价值可能.
