SQL加入日期范围？

交易金额为外币：

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列的类型)最小价值可能.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!