C# Winapp ขอความช่วยเหลือเกี่ยวกับระบบหนี้หน่อยครับ ผมจะทำให้มันอัพเดตอัตโนมัติ

--Compound interest Formula FV = PV x (1 + r)^n
--FV = Future Value
--PV = Present Value
--r = Annual interrest rate (per period, not per year)
--n = Number Of Periods.

USE tempdb
GO

DECLARE @AnnualRate NUMERIC(10, 8) = 0.001; -- 3% เดือน --> 3%/(30 * 100) = 0.001/วัน
DECLARE @StartDate DATE = '2013-08-26';
DECLARE @EndDate DATE = '2013-09-01';
DECLARE @ButNow DATE = GETDATE();

-- *****เตรียมข้อมูลเพื่อทดสอบ 
;WITH cte (LoanID, Principle, InterestRate , Interest, LastUpdate )
AS (
	SELECT 1 As LoanID, 10000.00, @AnnualRate, CONVERT(NUMERIC(18,8),0), CAST(@StartDate AS DATE) 	
	UNION ALL	
	SELECT 1 + LoanID, 10000.00, @AnnualRate, CONVERT(NUMERIC(18,8),0),  DATEADD(dd, 1, LastUpdate)
	FROM cte s
	WHERE DATEADD(dd, 1, LastUpdate) <= CAST(@EndDate AS DATE)
	)
SELECT * INTO #Temp
FROM cte 
OPTION (MAXRECURSION 32767)
-- เตรียมข้อมูลเพื่อทดสอบ*****

UPDATE #Temp 
SET Interest = Principle * POWER(1 + @AnnualRate, DATEDIFF(day, LastUpdate, @ButNow)),
	LastUpdate = @ButNow
WHERE Principle > 0

SELECT * FROM #Temp
DROP TABLE #Temp

/*Credit : Brad Schulz 
http://bradsruminations.blogspot.com/2009/11/loan-at-last.html

The monthly payment amount, c, is expressed by the following formula:

Formula : c = Px(r/(1-(1+r)^-M)
P is the loan amount, M is the Number of months of the loan,and r is the monthly interest rate expressed as a decimal.
The balance of the loan after payment number N is expressed by the following formula:
B_N = ((1+r)^N) x P - c((1+r)^N -1)/r

*/
USE tempdb
GO

DECLARE @LoanAmount NUMERIC(10, 2) = 10000 --ยอดเงินกู้ 
DECLARE @AnnualRate NUMERIC(10, 8) = 6.8 --อัตราดอกเบี้ย/ปี
DECLARE @NumberOfMonths INT = 48 -- four year
DECLARE @StartDate DATETIME = '2013-12-01';

--Principle, Capital = เงินต้น
--Interest = ดอกเบี้ย
--Compound interest = ดอกเบี้ยทบต้น

WITH InputVariables
AS (
	SELECT P, M, R,	C = ROUND((P * R) / (1 - POWER(1 + R, - M)), 2)
	FROM (
		SELECT P = @LoanAmount,	M = @NumberOfMonths, R = @AnnualRate / 12 / 100
		) InputData
	),
MonthlyPayments (
	PmtNo,
	PmtDate,
	Balance,
	Principle,
	Interest,
	CumulPrinciple,
	CumulInterest
	)
AS (
	SELECT X, 
	    @StartDate,
		--DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @StartDate), '19000101'),
		CAST(NewBalance AS NUMERIC(10, 2)),
		CAST(P - NewBalance AS NUMERIC(10, 2)),
		CAST(C - (P - NewBalance) AS NUMERIC(10, 2)),
		CAST(P - NewBalance AS NUMERIC(10, 2)),
		CAST(C - (P - NewBalance) AS NUMERIC(10, 2))
	FROM InputVariables
	CROSS APPLY (
		SELECT X = 1
		) CalcPmtNo
	CROSS APPLY (
		SELECT NewBalance = ROUND(P * POWER(1 + R, X) - (POWER(1 + R, X) - 1) * C / R, 2)
		) CalcNewBalance
	
	UNION ALL
	
	SELECT X,
		DATEADD(MONTH, 1, mp.PmtDate),
		CAST(NewBalance AS NUMERIC(10, 2)),
		CAST(mp.Balance - NewBalance AS NUMERIC(10, 2)),
		CAST(C - (mp.Balance - NewBalance) AS NUMERIC(10, 2)),
		CAST(mp.CumulPrinciple + mp.Balance - NewBalance AS NUMERIC(10, 2)),
		CAST(mp.CumulInterest + C - (mp.Balance - NewBalance) AS NUMERIC(10, 2))
	FROM MonthlyPayments mp
	CROSS JOIN InputVariables
	CROSS APPLY (
		SELECT X = mp.PmtNo + 1
		) CalcPmtNo
	CROSS APPLY (
		SELECT NewBalance = CASE 
				WHEN X = M
					THEN 0.00 /* Last Payment */
				ELSE 
				    ROUND(P * POWER(1 + R, X) - (POWER(1 + R, X) - 1) * C / R, 2)
				END
		) CalcNewBalance
	WHERE X <= M
	)
SELECT PmtNo,
	PmtMonth = DATENAME(MONTH, PmtDate) + STR(YEAR(PmtDate), 5),
	PmtAmount = Principle + Interest,
	Principle,
	Interest,
	CumulPrinciple,
	CumulInterest,
	Balance
FROM MonthlyPayments
ORDER BY PmtNo
OPTION (MAXRECURSION 32767);