Aamir Khan
Reputation: 57
Efficient alternate to the cursor in TSQL 2012 to get last value for payment schedule calculation
I am trying to derive an annuity payment plan. I know there are some solution available online but as I need to make changes to monthly payment amounts, I can't use those solutions and due to security policies I can't install new packages as well. Right now I am struggling to calculate the monthly opening balance as it depends on the capital calculated in the last row. Is there a more efficient way to do this other then the cursor. I have like 30 million rows right now.
Data I have:
This is a very simple example, there can be different rates, different monthly payments and dates missing. So I can't use the available solution online.
End result I want:
where:
- Interest = Opening Balance x Rate Charge
- Capital = Monthly repayment - Interest
- Opening Balance = Last Opening Balance - Last Capital
I have tried to use LAG but it does not work as LAG is a deterministic function.
Here is the script for the data with account.
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
Upvotes: 0
Views: 40
Answers (2)
iamdave
Reputation: 12243
Because your interest rate is not consistent and you cannot therefore use an interest rate calculation, you will not get any more efficient than going row by row.
Whilst not the greatest performance-wise, you will see a more efficient execution utilising a Recursive Common Table Expression (CTE), that joins to itself and checks the one row within the derived table with the next one incoming, which in your case is the next month you need to calculate the values for.
Because this row already exists in full within the CTE, you can reference the calculated values as you would in a 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.
Output:
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
| 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 |
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
Upvotes: 1
etsa
Reputation: 5060
You can try something like this, using CTE.
In the CTE construction, it use ROW_NUMBER to link each row with preceding one and calculate the various amount as you described.
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;
Output:
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
Upvotes: 1
Related Questions
- How to know last row when iterating cursor on SQL Server?
- T SQL cursor calculation based on previous rows
- TSQL - How do I analyse the previous row to establish time lapsed between payments
- how to get last processed rows in cursor
- How could I re-write this cursor to calculate running total?
- How to get the last iteration in cursor prematurely?
- Using previous rows calculated value
- How to get Latest value of row in Cursor result set in SQL
- selecting the last payment date using SQL
- getting last record of a cursor