基于日期的case语句查询

Question

我早些时候发布了这个查询。再发一次详细信息，以帮助更好地理解我的问题。

原始数据集

Name          currency  lcfeerate   effectivestartdate
Institution1    USD      0.0029      7/9/2009
Institution1    CAD      0.0029      7/9/2009
Institution1    USD      0.0034      4/3/2017
Institution2    CAD      0.0029      7/9/2009
Institution2    USD      0.0029      7/9/2009
Institution3    CAD      0.0029      7/9/2009
Institution3    USD      0.0029      7/9/2009
Institution3    USD      0.0034      4/3/2017
Institution3    CAD      0.0034      4/3/2017

我需要运行查询，例如返回一行，每一行对应于每个机构和相应的货币。也就是说，Institution1将有2行，美元和民航处各有1行。同样，第2和第3机构将各有2排。因此，最终的结果是一个6行的表。筛选表的规则是，对于每个机构和货币，根据有效日期选择lcfeerate。当生效日期介于声明的开始日期和结束日期之间时，将为该生效日期选择发热日期。当声明的开始日期和结束日期之间没有生效日期时，它将检查前一个最大的生效日期。以下是所需输出的两个示例。示例1

Start date- 1/1/2017        
End date-   3/31/2017       
Name         currency   lcfeerate   effectivestartdate
Institution1    USD      0.0029        7/9/2009
Institution1    CAD      0.0029        7/9/2009
Institution2    CAD      0.0029        7/9/2009
Institution2    USD      0.0029        7/9/2009
Institution3    CAD      0.0029        7/9/2009
Institution3    USD      0.0029        7/9/2009

由于在声明的开始日期和结束日期之间没有有效的开始日期，所以它选择了下一个有效开始日期7/9/2009，并为每个机构和货币、美元和CAD提供了对应于这些日期的lcfeerate。

示例2

Start date- 4/1/2017        
End date-   5/31/2017       
Name         currency   lcfeerate   effectivestartdate
Institution1    CAD       0.0029      7/9/2009
Institution1    USD       0.0034      4/3/2017
Institution2    CAD       0.0029      7/9/2009
Institution2    USD       0.0029      7/9/2009
Institution3    USD       0.0034      4/3/2017
Institution3    CAD       0.0034      4/3/2017

在这种情况下，由于机构3的有效日期为2017年3月4日，它是介于宣布的开始日期和结束日期之间，它为它提供了新的方法。对于institution1，美元汇率在声明的日期之间有生效日期，因此在声明的日期之间提供了一个和rest 3行之间没有有效日期的日期，因此选择了前一个生效日期来提供lcfeerate。

很容易根据有效日期和组按名称和货币对表进行排序，以获得最高的值，但我不知道如何编写这个查询。

我尝试过的查询如下：

declare @startdate as datetime = '1-Jan-2017';
declare @enddate as datetime = '31-Mar-2017';

select bankname, lcfeerate
        ,case when effectivestartdate between @startdate and @enddate then lcfeerate 
           when effectivestartdate not between @startdate and @enddate
        then (select *
        from (
              select *, row_number()
              over (partition by name, currency order by effectivestartdate desc) as seqnum
                from table1
             ) t1
        where seqnum = 1)end as lcfeerate
     from table1

我得到以下错误：

当子查询未引入EXISTS时，只能在select列表中指定一个表达式。

Answer 1

你很接近，但你的排名错了。第一个排序标准应该是日期是否在日期范围内，第二个标准是按降序排列的日期。

select name, currency, lcfeerate, effectivestartdate
from
(
  select 
    name, currency, lcfeerate, effectivestartdate, 
    row_number() over (partition by name, currency order by
          case when effectivestartdate between @startdate and @enddate the 1 else 2 end,
          effectivestartdate desc) as rn
  from table1
) ranked;

Answer 2

declare @startdate as datetime = '1-Jan-2017';
declare @enddate as datetime = '31-Mar-2017';

select bankname, lcfeerate
        ,case when effectivestartdate between @startdate and @enddate then lcfeerate 
           when effectivestartdate not between @startdate and @enddate
        then (select top 1 lcfeerate
        from (
              select lcfeerate, row_number()
              over (partition by name, currency order by effectivestartdate desc) as seqnum
                from table1
             ) t1
        where seqnum = 1)end as lcfeerate
     from table1

我希望这对你有帮助。