Вкратце: завершенный запрос — это последний блок кода в конце этого длинного объяснения.
Давайте рассмотрим это шаг за шагом, а затем представим окончательное решение в виде одного запроса. Для решения этой проблемы необходимо выполнить несколько шагов.
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
)
Поместите это в приведенный выше запрос, чтобы получить
; 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
person
KindaTechy
schedule
03.03.2017
