sql - T-SQL 覆盖特价并生成最终日期范围

Question

我有一个交易表，其中包含日期范围和该范围的基本费率。我有另一个特价表，其中包含特价的日期范围及其价格。如果特价在交易日期范围内，我想将我的原始交易分成多条记录。

为了简单起见，我创建了两个列数有限的表

DECLARE @ClientTrx AS TABLE (ClientId int, StartDate Date, EndDate Date, Rate decimal(10,2))
DECLARE @SpecialRate AS TABLE (ClientId int, StartDate Date, EndDate Date, Rate decimal(10,2))

insert into @ClientTrx select 1, '1/1/2020', '1/15/2020', 10
insert into @ClientTrx select 1, '1/16/2020', '1/31/2020', 10
insert into @ClientTrx select 2, '1/1/2020', '1/15/2020', 20
insert into @ClientTrx select 2, '1/16/2020', '1/31/2020', 20
insert into @ClientTrx select 2, '2/1/2020', '2/13/2020', 20

insert into @SpecialRate select 1, '12/25/2019', '1/3/2020', 13
insert into @SpecialRate select 1, '1/4/2020', '1/6/2020', 15
insert into @SpecialRate select 1, '1/11/2020', '1/18/2020', 12

insert into @SpecialRate select 2, '1/25/2020', '1/31/2020', 23
insert into @SpecialRate select 2, '2/4/2020', '2/8/2020', 25
insert into @SpecialRate select 2, '2/11/2020', '2/29/2020', 22

我需要帮助编写一个产生以下结果的查询:

ClientId    StartDate   EndDate Rate
1   2020-01-01  2020-01-03  13.00   special rate
1   2020-01-04  2020-01-06  15.00   special rate
1   2020-01-07  2020-01-10  10.00   regular rate
1   2020-01-11  2020-01-15  12.00   special rate
1   2020-01-16  2020-01-18  12.00   special rate splitting pay period
1   2020-01-19  2020-01-31  10.00   regular rate
2   2020-01-01  2020-01-15  20.00   regular rate  
2   2020-01-16  2020-01-24  20.00   regular rate
2   2020-01-25  2020-01-31  23.00   special rate
2   2020-02-01  2020-02-03  20.00   regular rate  
2   2020-02-04  2020-02-08  25.00   special rate
2   2020-02-09  2020-02-10  20.00   regular rate 
2   2020-02-11  2020-02-13  22.00   special rate

我认为可以使用 CTE，但我想不出来。有人可以帮忙吗？

注意:我对我的输入和预期输出做了一些更改，我想我需要一个组级别，你能帮忙吗？

Answer 1

这是一种使用临时计数表来扩展数据集，然后应用 Gaps-and-Islands 进行最终汇总的方法

示例

;with cte as (
Select A.ClientId
      ,D    
      ,Rate = coalesce(NewRate,A.Rate)
      ,Grp  = datediff(day,'1900-01-01',D) - row_number() over (partition by ClientID,coalesce(NewRate,A.Rate) Order by D)
 From  @ClientTrx A
 Cross Apply ( 
                Select Top (DateDiff(DAY,StartDate,EndDate)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),StartDate) 
                 From  master..spt_values n1,master..spt_values n2 
             ) B
 Outer Apply (
               Select NewRate=Rate 
                 From @SpecialRate
                 Where D between StartDate and EndDate
                   and ClientId=A.ClientID
             ) C
)
Select ClientID
      ,StartDate= min(D)
      ,EndDate  = max(D)
      ,Rate     = Rate
 From  cte
 Group By ClientID,Grp,Rate
 Order by ClientID,min(D)

返回

ClientID    StartDate   EndDate     Rate
1           2020-01-01  2020-01-03  13.00
1           2020-01-04  2020-01-06  15.00
1           2020-01-07  2020-01-10  10.00
1           2020-01-11  2020-01-18  12.00
1           2020-01-19  2020-01-31  10.00
2           2020-01-01  2020-01-24  20.00
2           2020-01-25  2020-01-31  23.00
2           2020-02-01  2020-02-03  20.00
2           2020-02-04  2020-02-08  25.00
2           2020-02-09  2020-02-10  20.00
2           2020-02-11  2020-02-15  22.00

注意事项:

Cross Apply B 为@ClientTrx 中介于startDate 和endDate 之间的每个日期生成一条记录。

外部 Apply C 尝试查找 Exception 或 NewRate

CTE 每个日期生成一个记录并切换默认或异常率。看起来像这样

注意 GRP 如何变化。这是“喂养”间隙和岛屿的简单技术

然后根据ClientID和Grp对cte的结果进行分组就成了一件小事