获取开始日期和结束日期对应的值

Question

我有一个表格，表格如下

Name    Currency    Rate    Effective date
Institution 1   USD 0.25    Sunday, June 05, 2016
Institution 1   CAD 0.35    Monday, December 05, 2016
Institution 1   USD 0.5 Monday, April 03, 2017
Institution 2   USD 0.25    Sunday, August 07, 2016
Institution 2   USD 0.28    Sunday, January 15, 2017
Institution 2   CAD 0.35    Saturday, April 20, 2013
Institution 2   CAD 0.25    Monday, March 06, 2017

我需要在SQL Server中为每个机构和货币分组的查询，获得开始日期和结束日期之间的生效日期的比率。如果声明的开始日期和结束日期之间没有生效日期，则查找小于开始日期的最后一个生效日期并获取相应的汇率。例如，如果开始日期是2017年1月1日，结束日期是2017年3月31日，则对于机构1，美元汇率将为0.25，CAD汇率将为0.35。同样，开始日期2017年4月1日和结束日期2017年5月31日，美元汇率将为0.5，CAD汇率将为0.35。

任何帮助我们都将不胜感激！

Answer 1

如果我理解正确的话，你可以查询如下：

Select * from (
     Select Name,   Currency,   Rate,   [Effective date],
        RowN = Row_Number() over (Partition by [Name], [Currency] Order by [Effective Date] desc)
     from yourTable 
     where [Effective Date] between @startDate and @EndDate ) a
    where a.RowN = 1

在其他方面

 Select top (1) with ties 
     Name,  Currency,   Rate,   [Effective date]
     from yourTable
     Where [Effective Date] between @startDate and @EndDate
     Order by Row_Number() over (Partition by [Name], [Currency] Order by [Effective Date] desc)

两者都在相同的逻辑中工作。

但这不包括您的要求“如果在声明的开始日期和结束日期之间没有生效日期，则查找早于开始日期的最后生效日期”，因为我不清楚。你能给我看更多的样品吗？

Answer 2

使用ROW_NUMBER()和OVER()是确定“最近”或“最新”行的有效方法。在OVER子句中是ORDER BY ...DESC，定位“最新”信息。

在这个问题中，需要在日期范围内有点误导，因为所有需要的是生效日期在该范围的结束日期之前，因此:小于结束日期涵盖了所有需要的结果，如下所示。

示例数据：

CREATE TABLE Table1
    ([Name] varchar(12), [Currency] varchar(3), [Rate] decimal(12,2), [EffectiveDate] datetime)
;

INSERT INTO Table1
    ([Name], [Currency], [Rate], [EffectiveDate])
VALUES
    ('Institution1', 'USD', 0.25, '2016-06-05 00:00:00'),
    ('Institution1', 'CAD', 0.35, '2016-12-05 00:00:00'),
    ('Institution1', 'USD', 0.5, '2017-04-03 00:00:00'),
    ('Institution2', 'USD', 0.25, '2016-08-07 00:00:00'),
    ('Institution2', 'USD', 0.28, '2017-01-15 00:00:00'),
    ('Institution2', 'CAD', 0.35, '2013-04-20 00:00:00'),
    ('Institution2', 'CAD', 0.25, '2017-03-06 00:00:00')
;

查询1

declare @EndDate as datetime 
set @EndDate = '20170331'

SELECT
      *
FROM (
      SELECT
            Name
          , Currency
          , Rate
          , EffectiveDate
          , ROW_NUMBER() OVER (PARTITION BY Name, Currency ORDER BY EffectiveDate DESC) AS rn
          , @EndDate EndDate
      FROM Table1
      WHERE EffectiveDate < @EndDate
      ) AS d
WHERE d.rn = 1
;

结果1

/*
If start date is Jan 1, 2017 and end date is Mar 31, 2017 
then for Institution 1, USD Rate will be 0.25 and CAD Rate will 0.35.
*/
+----+--------------+----------+------+---------------+----+------------+
|    |     Name     | Currency | Rate | EffectiveDate | rn |  EndDate   |
+----+--------------+----------+------+---------------+----+------------+
|  1 | Institution1 | CAD      | 0.35 | 05/12/2016    |  1 | 31/03/2017 |
|  2 | Institution1 | USD      | 0.25 | 05/06/2016    |  1 | 31/03/2017 |
|  3 | Institution2 | CAD      | 0.25 | 06/03/2017    |  1 | 31/03/2017 |
|  4 | Institution2 | USD      | 0.28 | 15/01/2017    |  1 | 31/03/2017 |
+----+--------------+----------+------+---------------+----+------------+

查询2

set @EndDate = '20170531'

SELECT
      *
FROM (
      SELECT
            Name
          , Currency
          , Rate
          , EffectiveDate
          , ROW_NUMBER() OVER (PARTITION BY Name, Currency ORDER BY EffectiveDate DESC) AS rn
          , @EndDate EndDate
      FROM Table1
      WHERE EffectiveDate < @EndDate
      ) AS d
WHERE d.rn = 1
;

结果2

/*
Similarly for start date Apr 1, 2017 and end date May 31, 2017 
USD Rate will be 0.5 and CAD Rate will be 0.35.
*/
+----+--------------+----------+------+---------------+----+------------+
|    |     Name     | Currency | Rate | EffectiveDate | rn |  EndDate   |
+----+--------------+----------+------+---------------+----+------------+
|  1 | Institution1 | CAD      | 0.35 | 05/12/2016    |  1 | 31/05/2017 |
|  2 | Institution1 | USD      | 0.50 | 03/04/2017    |  1 | 31/05/2017 |
|  3 | Institution2 | CAD      | 0.25 | 06/03/2017    |  1 | 31/05/2017 |
|  4 | Institution2 | USD      | 0.28 | 15/01/2017    |  1 | 31/05/2017 |
+----+--------------+----------+------+---------------+----+------------+