Эффективная альтернатива курсору в TSQL 2012 для получения последнего значения для расчета графика платежей
Я пытаюсь получить план аннуитетных платежей. Я знаю, что в Интернете есть какое-то решение, но, поскольку мне нужно внести изменения в ежемесячные суммы платежей, я не могу использовать эти решения и из-за политик безопасности не могу также устанавливать новые пакеты. Прямо сейчас я изо всех сил пытаюсь вычислить ежемесячный начальный баланс, поскольку он зависит от капитала, рассчитанного в последней строке. Есть ли более эффективный способ сделать это, кроме курсора. У меня сейчас 30 миллионов строк.
Данные у меня есть:
Это очень простой пример, могут быть разные ставки, разные месячные платежи и пропущенные даты. Поэтому я не могу использовать доступное решение онлайн.
Конечный результат, который я хочу:
где:
- Проценты = начальное сальдо х ставка тарифа
- Капитал = Ежемесячное погашение - Проценты
- Начальный баланс = последний начальный баланс - последний капитал
Я пытался использовать LAG но это не работает как LAG является детерминированной функцией.
Вот скрипт для данных с учётной записью.
CREATE TABLE [temp_da_test].[example_3](
[Account] [nvarchar](255) NULL,
[Month] [date] NULL,
[Rate Charge] [float] NULL,
[Opening Balance] [money] NULL,
[Monthly Repayment] [money] NULL,
[Interest] [money] NULL,
[Capital] [money] NULL
) ON [PRIMARY]
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-06-30' AS Date), 0.014419, 2000.0000, 0.0000, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-06-30' AS Date), 0.014419, 4000.0000, 0.0000, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
2 ответа
Решение
Поскольку ваша процентная ставка не согласована, и поэтому вы не можете использовать расчет процентной ставки, вы не станете более эффективным, чем построчно.
Хотя производительность не самая высокая, вы увидите более эффективное выполнение с использованием рекурсивного общего табличного выражения (CTE), тот joins для себя и проверяет одну строку в производной таблице со следующей входящей, которая в вашем случае является следующим месяцем, для которого необходимо рассчитать значения.
Поскольку этот ряд уже существует полностью в CTEВы можете ссылаться на рассчитанные значения, как в cursor:
declare @t table(Account nvarchar(5),[Month] date null,[Rate Charge] float null,[Opening Balance] money null,[Monthly Repayment] money null);
insert into @t (Account, [Month], [Rate Charge], [Opening Balance], [Monthly Repayment]) values
(N'A', CAST(N'2005-06-30' AS Date), 0.014419, 2000.0000, 0.0000),(N'A', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 35.8800),(N'A', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 35.8800)
,(N'B', CAST(N'2005-06-30' AS Date), 0.014419, 4000.0000, 0.0000),(N'B', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 71.7600),(N'B', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 71.7600)
;
with d as
(
select Account
,[Month]
,[Rate Charge]
,[Opening Balance]
,[Monthly Repayment]
,row_number() over (order by Account,[Month]) as rn
from @t
)
,c as
(
-- Start with just the first row from your source data:
select top 1 Account
,[Month]
,[Rate Charge]
,cast([Opening Balance] as decimal(12,2)) as [Opening Balance]
,[Monthly Repayment]
,[Opening Balance] * [Rate Charge] as Interest
,[Monthly Repayment] - ([Opening Balance] * [Rate Charge]) as Capital
,rn
from d
order by [Month]
union all -- Then add on the next row until the end of the dataset is reached:
select d.Account
,d.[Month]
,d.[Rate Charge]
,case when c.Account <> d.Account -- When we hit a new account, restart the calculations:
then cast(d.[Opening Balance] as decimal(12,2))
else cast(c.[Opening Balance] - c.Capital as decimal(12,2))
end as [Opening Balance]
,d.[Monthly Repayment]
,case when c.Account <> d.Account
then d.[Opening Balance] * d.[Rate Charge]
else (c.[Opening Balance] - c.Capital) * d.[Rate Charge]
end as Interest
,case when c.Account <> d.Account
then d.[Monthly Repayment] - (d.[Opening Balance] * d.[Rate Charge])
else d.[Monthly Repayment] - ((c.[Opening Balance] - c.Capital) * d.[Rate Charge])
end as Capital
,d.rn
from c
join d
on c.rn = d.rn-1
)
select Account
,[Month]
,[Rate Charge]
,[Opening Balance]
,[Monthly Repayment]
,Interest
,Capital
from c
order by rn
option (maxrecursion 0) -- No matter how big the dataset is.
Выход:
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
| Account | Month | Rate Charge | Opening Balance | Monthly Repayment | Interest | Capital |
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
| A | 2005-06-30 | 0.014419 | 2000.00 | 0.00 | 28.838 | -28.838 |
| A | 2005-07-31 | 0.0149 | 2028.84 | 35.88 | 30.2296862 | 5.6503138 |
| A | 2005-08-31 | 0.0149 | 2023.19 | 35.88 | 30.14552632438 | 5.73447367562001 |
| A | 2005-09-30 | 0.014419 | 2017.46 | 35.88 | 29.0896912340712 | 6.79030876592877 |
| A | 2005-10-31 | 0.0149 | 2010.67 | 35.88 | 29.9589783993877 | 5.92102160061234 |
| A | 2005-11-30 | 0.014419 | 2004.75 | 35.88 | 28.9064755195408 | 6.97352448045923 |
| A | 2005-12-31 | 0.0149 | 1997.78 | 35.88 | 29.7668694852412 | 6.11313051475884 |
| A | 2006-01-31 | 0.0149 | 1991.67 | 35.88 | 29.6758363553301 | 6.20416364466991 |
| A | 2006-02-28 | 0.013458 | 1985.47 | 35.88 | 26.72039922567 | 9.15960077432997 |
| A | 2006-03-31 | 0.0149 | 1976.31 | 35.88 | 29.4470249484625 | 6.43297505153752 |
| A | 2006-04-30 | 0.014419 | 1969.88 | 35.88 | 28.4036568227319 | 7.47634317726812 |
| A | 2006-05-31 | 0.0149 | 1962.40 | 35.88 | 29.2398144866587 | 6.64018551334129 |
| A | 2006-06-30 | 0.014419 | 1955.76 | 35.88 | 28.2001007650831 | 7.67989923491687 |
| A | 2006-07-31 | 0.0149 | 1948.08 | 35.88 | 29.0263935013997 | 6.85360649860026 |
| A | 2006-08-31 | 0.0149 | 1941.23 | 35.88 | 28.9242732631709 | 6.95572673682915 |
| A | 2006-09-30 | 0.014419 | 1934.27 | 35.88 | 27.8903007461817 | 7.98969925381834 |
| A | 2006-10-31 | 0.0149 | 1926.28 | 35.88 | 28.7015764811181 | 7.1784235188819 |
| A | 2006-11-30 | 0.014419 | 1919.10 | 35.88 | 27.6715256312812 | 8.20847436871876 |
| A | 2006-12-31 | 0.0149 | 1910.89 | 35.88 | 28.4722837319061 | 7.40771626809391 |
| A | 2007-01-31 | 0.0149 | 1903.48 | 35.88 | 28.3618860276054 | 7.5181139723946 |
| B | 2005-06-30 | 0.014419 | 4000.00 | 0.00 | 57.676 | -57.676 |
| B | 2005-07-31 | 0.0149 | 4057.68 | 71.76 | 60.4593724 | 11.3006276 |
| B | 2005-08-31 | 0.0149 | 4046.38 | 71.76 | 60.29105264876 | 11.46894735124 |
| B | 2005-09-30 | 0.014419 | 4034.91 | 71.76 | 58.1793824681425 | 13.5806175318575 |
| B | 2005-10-31 | 0.0149 | 4021.33 | 71.76 | 59.9178077987753 | 11.8421922012247 |
| B | 2005-11-30 | 0.014419 | 4009.49 | 71.76 | 57.8128047006505 | 13.9471952993495 |
| B | 2005-12-31 | 0.0149 | 3995.54 | 71.76 | 59.5335877900397 | 12.2264122099603 |
| B | 2006-01-31 | 0.0149 | 3983.31 | 71.76 | 59.3513724580716 | 12.4086275419284 |
| B | 2006-02-28 | 0.013458 | 3970.90 | 71.76 | 53.4403906705407 | 18.3196093294593 |
| B | 2006-03-31 | 0.0149 | 3952.58 | 71.76 | 58.8934478209911 | 12.8665521790089 |
| B | 2006-04-30 | 0.014419 | 3939.71 | 71.76 | 56.8067282041309 | 14.9532717958691 |
| B | 2006-05-31 | 0.0149 | 3924.76 | 71.76 | 58.4788752502416 | 13.2811247497585 |
| B | 2006-06-30 | 0.014419 | 3911.48 | 71.76 | 56.3996139022332 | 15.3603860977668 |
| B | 2006-07-31 | 0.0149 | 3896.12 | 71.76 | 58.0521822471433 | 13.7078177528567 |
| B | 2006-08-31 | 0.0149 | 3882.41 | 71.76 | 57.8479415154824 | 13.9120584845176 |
| B | 2006-09-30 | 0.014419 | 3868.50 | 71.76 | 55.7798718187117 | 15.9801281812883 |
| B | 2006-10-31 | 0.0149 | 3852.52 | 71.76 | 57.4025460900988 | 14.3574539099012 |
| B | 2006-11-30 | 0.014419 | 3838.16 | 71.76 | 55.3424657520731 | 16.4175342479269 |
| B | 2006-12-31 | 0.0149 | 3821.74 | 71.76 | 56.9439627397059 | 14.8160372602941 |
| B | 2007-01-31 | 0.0149 | 3806.92 | 71.76 | 56.7231670448216 | 15.0368329551784 |
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
Вы можете попробовать что-то вроде этого, используя CTE.
В конструкции CTE он использует ROW_NUMBER, чтобы связать каждую строку с предыдущей и вычислить различную сумму, как вы описали.
WITH X AS (SELECT [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Monthly Repayment]-[Opening Balance]* [Rate Charge] AS [Capital]
, ROW_NUMBER() OVER (ORDER BY [Month]) AS RN
FROM [example_2]
)
, X2 AS (SELECT [Month]
, [Rate Charge]
, [Opening Balance]
, [Monthly Repayment]
, [Interest]=[Opening Balance]*[Rate Charge]
, [Capital]
, RN
FROM X WHERE RN=1
UNION ALL
SELECT X.[Month]
, X.[Rate Charge]
, CAST( XX1.[Opening Balance]-XX1.Capital AS money) AS [Opening Balance]
, X.[Monthly Repayment]
, (XX1.[Opening Balance]-XX1.Capital)*X.[Rate Charge] AS [Interest]
, X.[Monthly Repayment]-(XX1.[Opening Balance]-XX1.Capital)*X.[Rate Charge] AS [Capital]
, X.RN
FROM X
INNER JOIN X2 XX1 ON X.RN=XX1.RN+1
)
SELECT [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]
FROM X2;
Выход:
Month Rate Charge Opening Balance Monthly Repayment Interest Capital
---------- ---------------------- --------------------- --------------------- ---------------------- ----------------------
2005-06-30 0.014419 2000.00 0.00 28.838 -28.838
2005-07-31 0.0149 2028.838 35.88 30.2296862 5.6503138
2005-08-31 0.0149 2023.1877 35.88 30.14549652438 5.73450347562
2005-09-30 0.014419 2017.4532 35.88 29.089657640685 6.79034235931497
2005-10-31 0.0149 2010.6629 35.88 29.9588765788462 5.9211234211538
2005-11-30 0.014419 2004.7418 35.88 28.9063716764904 6.97362832350962
2005-12-31 0.0149 1997.7682 35.88 29.7667457579797 6.1132542420203
2006-01-31 0.0149 1991.6549 35.88 29.6756586917939 6.2043413082061
2006-02-28 0.013458 1985.4506 35.88 26.7201936188742 9.15980638112584
2006-03-31 0.0149 1976.2908 35.88 29.4467328249212 6.43326717507878
2006-04-30 0.014419 1969.8575 35.88 28.4033757658025 7.47662423419746
2006-05-31 0.0149 1962.3809 35.88 29.2394750489105 6.64052495108955
2006-06-30 0.014419 1955.7404 35.88 28.1998204678302 7.68017953216976
2006-07-31 0.0149 1948.0602 35.88 29.0260972849707 6.85390271502934
2006-08-31 0.0149 1941.2063 35.88 28.9239738295461 6.95602617045394
2006-09-30 0.014419 1934.2503 35.88 27.8899546983482 7.99004530165178
2006-10-31 0.0149 1926.2603 35.88 28.7012777950054 7.17872220499462
2006-11-30 0.014419 1919.0816 35.88 27.6712372702262 8.20876272977382
2006-12-31 0.0149 1910.8728 35.88 28.4720052753264 7.40799472467363
2007-01-31 0.0149 1903.4648 35.88 28.3616255986024 7.51837440139764