sql-server - 跨多个利率计算利息

Question

我有一个存储利率的表格，每个表格都有一个适用的开始日期。表中日期较晚的条目取代较早的条目。我必须使用开始日期、结束日期和金额查询此表。根据这些值，我需要得出一个总利息金额，该金额考虑了日期跨度的不同利率。

CREATE TABLE [dbo].[Interest_Rates](
[Interest_Rate] [float] NULL,
[Incept_Date] [datetime] NULL
) ON [PRIMARY]
GO

我有四个利率“区间”:

INSERT [dbo].[Interest_Rates] ([Interest_Rate], [Incept_Date]) VALUES (10, CAST(N'2001-05-03 11:12:16.000' AS DateTime))
GO
INSERT [dbo].[Interest_Rates] ([Interest_Rate], [Incept_Date]) VALUES (11.5, CAST(N'2014-01-07 10:49:28.433' AS DateTime))
GO
INSERT [dbo].[Interest_Rates] ([Interest_Rate], [Incept_Date]) VALUES (13.5, CAST(N'2016-03-01 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Interest_Rates] ([Interest_Rate], [Incept_Date]) VALUES (15.5, CAST(N'2016-05-01 00:00:00.000' AS DateTime))
GO

我想知道的是，是否可以计算从利率为 11.5% 的时间开始到利率为 11.5% 的稍后时间结束的一段时间的利率在一次查询中上升了两倍，达到 13.5%。

似乎可以使用精彩的 Suprotim Agarwal 示例来计算每个“波段”的利息，如下所示:

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @Amount Float

SET @StartDate = '2014-04-22'
SET @EndDate = '2016-04-13'
SET @Amount = 150000.00

SELECT
@Amount*(POWER(1.1550, CONVERT(NUMERIC(8,3),
DATEDIFF(d, @StartDate, @EndDate)/365.25))) - @Amount
as TotalInterest

(上例中利率为 15.5%)

我遇到困难的地方在于如何将计算与利率表相互关联，以便连接考虑日期跨度的每个子部分属于哪个“区间”。

如有任何帮助或建议，我们将不胜感激。

Answer 1

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() 命令中。使用 @StartDate 和 @EndDate 不会为我们提供以每个特定费率花费的天数的准确计数。我们在使用 inter_begin 和 inter_end 时遇到了同样的问题。相反，我们必须使用 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
)

把这个放在上面的Query中得到

; 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 中并取 Actual_interest 字段的总和:

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