计算多种利率的利息 [英] Calculating interest across multiple interest rates
问题描述
我有一个表，用于存储利率，每个利率都有适用的开始日期.表中的较晚条目将取代较早的条目.我必须用开始日期，结束日期和金额查询该表.从这些值中，我需要得出一个总利息金额，该总利息金额应考虑到该日期跨度的不同利率.
I have a table where I store interest rates, each with a start date where it became applicable. Later-dated entries in the table supersede earlier entries. I have to query this table with a start date, an end date, and an amount. From these values I need to end up with an overall interest amount that takes the different interest rates for the date span into account.
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％的时间开始，直到利率达到在一次查询中上升了两次，达到13.5％.
What I'd like to know is whether it's possible to calculate the interest rate for a period of time beginning at a time when the interest rate was, say, 11.5%, and ending at a later time when the interest rate has risen twice to 13.5%, within a single query.
似乎可以使用精彩的 Suprotim Agarwal 的示例完成每个带"的兴趣计算，如下所示:
It seems like the interest calculation for each 'band' can be done using the wonderful Suprotim Agarwal's example as follows:
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％)
(Interest rate at 15.5% in above example)
我陷入困境的地方是研究如何将计算与利率"表相互关联，以便联接考虑日期跨度的每个小节属于哪个带".
Where I'm getting stuck is at working out how to interrelate the calculation with the Interest Rates table such that the join takes into account which 'band' each subsection of the date span falls into.
任何帮助或建议，将不胜感激.
Any help or advice would be much appreciated.
推荐答案
tl; dr:完整的查询是该详细说明结尾的最后一个代码块.
tl;dr: the completed query is the last code block at the end of this long explanation.
让我们逐步完成此步骤，然后将最终解决方案作为一个查询提出.需要几个步骤来解决这个问题.
Let's walk through this step-by-step and then present the final solution as one query. A few steps are needed to solve this problem.
1)弄清楚我们期望的日期范围涵盖了什么
1) Figure out which rates our desired date range covers
2)设计一种巧妙的方法来选择这些费率
2) Devise a clever way to choose those rates
3)以使我们产生应计总利息的方式组合这些日期和利率.
3) Combine those dates and rates in such a way to give us that total interest accrued.
一些初步说明
由于您的示例利率计算将日期视为其最佳解决方案，因此我只使用数据类型 date 而不是 datetime .如果您需要更好的分辨率，请告诉我，我可以进行更新.
Since your example calculation of interest rate considers days as its finest resolution, I just use datatypes date instead of datetime. If you need a finer resolution, let me know and I can update.
我正在使用以下声明的变量
I'm using the following declared variables
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表列出了这样的日期:
Right now, your interest_rates table lists dates like this:
+ ------------- + ----------- +
| interest_rate | incept_date |
+ ------------- + ----------- +
| 10 | 2001-05-03 |
| 11.5 | 2014-01-07 |
| 13.5 | 2016-03-01 |
| 15.5 | 2016-05-01 |
+ ------------- + ----------- +
但是您希望它列出这样的间隔:
But you want it to list intervals like this:
+ ------------- + ------------ + ------------ +
| 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 |
+ ------------- + ------------ + ------------ +
以下查询可以将您的日期列表变成间隔:
The following query can turn your date list into intervals:
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()命令在使用日期算法有点松散.
通过这样跟踪日期间隔，可以更轻松地选择适用的费率.
Keeping track of the date intervals like this makes selecting the applicable rates much easier.
2)选择费率
现在，通过使用上述查询作为CTE，我们可以选择位于所需范围内的记录.该查询有些棘手，因此需要一些时间才能真正理解它.
Now we can select records that sit within our desired range by using the above query as a CTE. This query is a little tricky, so take some time to really understand it.
; 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
)
这有效地过滤掉了我们不关心的任何利率，并留下了
This effectively filters out any rates we don't care about and leaves us with
+ ------------- + ------------ + ------------ +
| 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语句，例如
Now we have everything we need, and calculating the interest is just a matter selecting the right things from this table. Most of what you wrote for your calculation remains the same; the main changes are in the datediff() command. Using @StartDate and @EndDate won't give us an accurate count of the days spent at each specific rate. We run into the same problem by using inter_begin and inter_end. Instead, we must use a case statement, something like
datediff(day,
case when @StartDate > inter_begin then @StartDate else inter_begin end,
case when @EndDate < inter_end then @EndDate else inter_end end
)
将其放在上面的查询中以获得
Put this in the above Query to get
; 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
这为我们提供了这张桌子
which gives us this table
+ ------------- + ------------ + ------------ + ----------- + --------------- +
| 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 字段的总和:
Finally, put this in a CTE and take the sum of the Actual_interest 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
这篇关于计算多种利率的利息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
