tl;dr：完成的查询是这个长解释末尾的最后一个代码块。
让我们逐步完成这一过程，然后将最终解决方案作为一个查询呈现。需要采取几个步骤来解决这个问题。
1) 找出我们所需的日期范围涵盖哪些费率
2）设计一种巧妙的方法来选择这些费率
3) 将这些日期和利率结合起来，得出应计利息总额。
一些初步说明
由于您的利率计算示例将天数视为其最佳分辨率，因此我只使用数据类型date代替datetime。如果您需要更精细的分辨率，请告诉我，我可以更新。
我正在使用以下声明的变量
declare @EndOfTime date = '2049-12-31' -- This is some arbitrary end of time value that I chose
declare @StartDate Date = '2012-04-22' -- I made this earlier to cover more rates
declare @EndDate Date = '2016-04-13'
declare @Amount Float = 100000.00 -- I changed it to a softer number
1) 日期间隔
现在，您的interest_rates 表列出了如下日期：
+ ------------- + ----------- +
| interest_rate | incept_date |
+ ------------- + ----------- +
| 10 | 2001-05-03 |
| 11.5 | 2014-01-07 |
| 13.5 | 2016-03-01 |
| 15.5 | 2016-05-01 |
+ ------------- + ----------- +
但你希望它列出这样的间隔：
+ ------------- + ------------ + ------------ +
| interest_rate | inter_begin | inter_end |
+ ------------- + ------------ + ------------ +
| 10 | 2001-05-03 | 2014-01-06 |
| 11.5 | 2014-01-07 | 2016-02-29 |
| 13.5 | 2016-03-01 | 2016-04-30 |
| 15.5 | 2016-05-01 | 2049-12-31 |
+ ------------- + ------------ + ------------ +
以下查询可以将日期列表转换为间隔：
select i1.interest_rate
, i1.incept_date as inter_begin
, isnull(min(i2.incept_date) - 1,@EndOfTime) as inter_end
from #interest i1
left join #interest i2 on i2.incept_date > i1.incept_date
group by i1.interest_rate, i1.incept_date
注意：我在这里对日期算术有点松散，没有使用 dateadd() 命令。
像这样跟踪日期间隔可以使选择适用的费率变得更加容易。
2) 选择费率
现在，我们可以使用上述查询作为 CTE 来选择位于所需范围内的记录。这个查询有点棘手，所以需要一些时间来真正理解它。
; with
intervals as (
-- The above query/table
)
select *
from intervals
where inter_begin >= (
select inter_begin -- selects the first rate covered by our desired interval
from intervals
where @StartDate between inter_begin and inter_end
)
and inter_end <= (
select inter_end -- selects the last rate covered by our desired interval
from intervals
where @EndDate between inter_begin and inter_end
)
这有效地过滤掉了我们不关心的任何费率，并为我们留下了
+ ------------- + ------------ + ------------ +
| interest_rate | inter_begin | inter_end |
+ ------------- + ------------ + ------------ +
| 10 | 2001-05-03 | 2014-01-06 |
| 11.5 | 2014-01-07 | 2016-02-29 |
| 13.5 | 2016-03-01 | 2016-04-30 |
+ ------------- + ------------ + ------------ +
3）计算利息
现在我们已经拥有了所需的一切，计算利息只需从该表中选择正确的内容即可。您为计算所写的大部分内容保持不变；主要更改在 datediff() 命令中。使用@开始日期 and @EndDate不会向我们提供按每个特定费率花费的天数的准确计数。我们通过使用遇到了同样的问题中间开始 and 中间结束。相反，我们必须使用 case 语句，例如
datediff(day,
case when @StartDate > inter_begin then @StartDate else inter_begin end,
case when @EndDate < inter_end then @EndDate else inter_end end
)
将其放入上面的查询中即可得到
; with
intervals as (...) -- same as above
select *
, DATEDIFF(day,
case when @StartDate > inter_begin then @StartDate else inter_begin end,
case when @EndDate < inter_end then @EndDate else inter_end end) as days_active
, @Amount*(POWER((1+interest_rate/100),
convert(float,
DATEDIFF(day,
case when @StartDate > inter_begin then @StartDate else inter_begin end,
case when @EndDate < inter_end then @EndDate else inter_end end
)
)/365.25)
) - @Amount as Actual_Interest
from ... -- same as above
这给了我们这张表
+ ------------- + ------------ + ------------ + ----------- + --------------- +
| interest_rate | inter_begin | inter_end | days_active | Actual_interest |
+ ------------- + ------------ + ------------ + ----------- + --------------- +
| 10 | 2001-05-03 | 2014-01-06 | 624 | 17683.63 |
| 11.5 | 2014-01-07 | 2016-02-29 | 786 | 26283.00 |
| 13.5 | 2016-03-01 | 2016-04-30 | 43 | 1501.98 |
+ ------------- + ------------ + ------------ + ----------- + --------------- +
最后，将其放入 CTE 中并求和实际利息 field:
declare @EndOfTime date = '2049-12-31' -- This is some arbitrary end of time value that I chose
declare @StartDate Date = '2012-04-22' -- I made this earlier to cover more rates
declare @EndDate Date = '2016-04-13'
declare @Amount Float = 100000.00 -- I changed it to a softer number
; with
intervals as (
select i1.interest_rate
, i1.incept_date as inter_begin
, isnull(min(i2.incept_date) - 1,@EndOfTime) as inter_end
from #interest i1
left join #interest i2 on i2.incept_date > i1.incept_date
group by i1.interest_rate, i1.incept_date
)
, interest as (
select *
, DATEDIFF(day,
case when @StartDate > inter_begin then @StartDate else inter_begin end,
case when @EndDate < inter_end then @EndDate else inter_end end) as days_active
, @Amount*(POWER((1+interest_rate/100),
convert(float,
DATEDIFF(day,
case when @StartDate > inter_begin then @StartDate else inter_begin end,
case when @EndDate < inter_end then @EndDate else inter_end end
)
)/365.25)
) - @Amount as Actual_Interest
from intervals
where inter_begin >= (
select inter_begin -- selects the first rate covered by our desired interval
from intervals
where @StartDate between inter_begin and inter_end
)
and inter_end <= (
select inter_end -- selects the last rate covered by our desired interval
from intervals
where @EndDate between inter_begin and inter_end
)
)
select sum(actual_interest) as total_interest
from interest
