Skip to content

Instantly share code, notes, and snippets.

@carpii
Created August 14, 2013 22:01
Show Gist options
  • Save carpii/6236105 to your computer and use it in GitHub Desktop.
Save carpii/6236105 to your computer and use it in GitHub Desktop.
Query to loop over finance data, populating the memory table with running totals of principal, compound interest etc
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment