Query to loop over finance data, populating the memory table with running totals of principal, compound interest etc

gistfile1.txt

SET NOCOUNT ON;

-- declare memory table 
DECLARE @totals TABLE (id int not null, effectiveDate datetime, daysSinceLastPayment int, type varchar(20), amount decimal(12,5), totalPrincipal decimal(12,5), totalFees decimal(12,5), totalPayments decimal(12,5), totalInterest decimal(12,5), balance decimal(12, 5))

insert into @totals (id, amount, type) select id, amount, type from finance

-- populate with some pretend dates 
update @totals set effectiveDate = DATEADD(DD, -100+id, DATEADD(MM, -6, GETDATE()));

-- populate DaysSinceLastPayment in memory table 
update a set a.daysSinceLastPayment = 
  (select TOP 1 DATEDIFF(dd, b.effectiveDate, a.effectiveDate) from @totals b where b.type = 'PAYMENT' and b.id <= a.id order by id DESC) 
from @totals a

-- loop over each record in finance resultset
DECLARE @id int, @effective_date datetime, @days_since int, @type varchar(30), @amount decimal(12,5)
DECLARE loop_cursor CURSOR FOR SELECT id, effectiveDate, daysSinceLastPayment, type, amount FROM @totals order by id

DECLARE @remainingPayment decimal(12, 5)
DECLARE @consumedPayment decimal(12, 5)

DECLARE @runningTotalFees decimal(12, 5)
DECLARE @runningTotalPrincipal decimal(12, 5)
DECLARE @runningTotalPayments decimal(12, 5)
DECLARE @runningTotalInterest decimal(12, 5)
DECLARE @curInterest decimal(12,5)

SELECT @runningTotalFees=0, @runningTotalPrincipal = 0, @runningTotalPayments = 0, @runningTotalInterest = 0

OPEN loop_cursor
FETCH NEXT FROM loop_cursor INTO @id, @effective_date, @days_since, @type, @amount;
WHILE @@FETCH_STATUS = 0
BEGIN
	-- work out interest up to this date
	SELECT @runningTotalInterest = @runningTotalInterest + (@runningTotalPrincipal * 0.05 * ISNULL(@days_since, 0));

	IF (@type = 'PRINCIPAL')
	BEGIN
		SELECT @runningTotalPrincipal = @runningTotalPrincipal + @amount;
	END

	IF (@type = 'FEE')
	BEGIN
		SELECT @runningTotalFees = @runningTotalFees + @amount;
	END

  IF (@type = 'PAYMENT')
	BEGIN
		SET @remainingPayment = @amount

		-- decrease Fees 
		IF (@remainingPayment > @runningTotalFees)
		BEGIN
			SELECT @remainingPayment = @remainingPayment - @runningTotalFees,
					@runningTotalFees = 0;
		END
		ELSE
		BEGIN
			SELECT @runningTotalFees = @runningTotalFees - @remainingPayment,
					@remainingPayment = 0;
		END

		-- decrease interest
		IF (@remainingPayment > @runningTotalInterest)
		BEGIN
			SELECT @remainingPayment = @remainingPayment - @runningTotalInterest,
					@runningTotalInterest = 0;
		END
		ELSE
		BEGIN
			SELECT @runningTotalInterest = @runningTotalInterest - @remainingPayment,
				@remainingPayment = 0;
		END

		-- decrease Principal
		IF (@remainingPayment > @runningTotalPrincipal)
		BEGIN
			SELECT @remainingPayment = @remainingPayment - @runningTotalPrincipal,
					@runningTotalPrincipal = 0;
		END
		ELSE
		BEGIN
			SELECT @runningTotalPrincipal = @runningTotalPrincipal - @remainingPayment,
				@remainingPayment = 0;
		END

		SET @runningTotalPayments = @runningTotalPayments + @amount;
	END


	update @totals 
	set 
			totalPrincipal = @runningTotalPrincipal, 
			totalPayments = @runningTotalPayments, 
			totalFees = @runningTotalFees, 
			totalInterest = @runningTotalInterest,
	 		balance = @runningTotalPrincipal + @runningTotalFees + @runningTotalInterest
	where 
			id = @id;

	FETCH NEXT FROM loop_cursor INTO @id, @effective_date, @days_since, @type, @amount;
END 
CLOSE loop_cursor;
DEALLOCATE loop_cursor;

SELECT * from @totals