Tuesday, September 6, 2011

ElevateDB: Stored Procedures Part 3

In my last ElevateDB Stored Procedure post I went on a rant about how frustrating my stored procedure journey became. Well, today is a new day and I feel better. A few more posts were added to the support thread I started with one really good one by the Tim Young the creator of ElevateDB.

The stored proc I was attempting to create is a fairly simple "Amortization Schedule". So here is how you go about building an Amortization Schedule from scratch.

Because I'm an old school programmer here's my very simple method of deciding how to do just about every programming task:

Gunny's Rules
  • 1. Identify the output
  • 2. Identify the inputs
  • 3. Ask ====> Are there enough inputs to create that output?
If the answer is NO there are not enough inputs to create that output, then go get the necessary inputs. It's a waste of time to begin writing code until you have all the inputs. If you spend a little time now ironing out these details you will save tons of time later.

This is really bone simple but it works. Or as I used to say to my Major... "It's Gump Proof" (Yeah I know I'm a Marine but it's still a cool saying. Calling something Gomer Proof doesn't have the same meaning.)

Identify The Output:
A database table that contains payment information for each monthly payment in an Amortization Schedule. To be more specific:
  • Payment Number
  • Payment Date
  • Balance Before Payment
  • Payment Amount
  • Principal Portion of Payment
  • Interest Portion or Payment
  • Balance After Payment
Identify The Inputs:
  • Payment Start Date
  • Loan Amount
  • Annual Percentage Rate (APR)
  • Length of Loan in Months
Ask ====> Are there enough inputs to create that output?

Answer: No. I need the payment amount.

Decision Time: Okay, there are two choices at this point.
  • Ask for this value to be supplied just like the other values
  • Calculate the payment based on the existing input values
I decide to calculate the payment. So now I need a loan payment formula. Let's assume I know where to get one of these. The major point is I identified an additional input was needed - A Formula. My list of inputs now looks like this:

Identify The Inputs:
  • Payment Start Date
  • Loan Amount
  • Annual Percentage Rate (APR)
  • Length of Loan in Months
  • Loan Payment Formula
Ask ====> Are there enough inputs to create that output?

Answer: No. I need two more bits of information.
  • Another formula for calculating the Interest Portion of the payment
  • A method for advancing the Payment Date by one month
No problem. That formula is pretty straigh forward and most SQL languages support Date Math. I'm good at this point. Let's have another look at the list of inputs.

Identify The Inputs:
  • Payment Start Date
  • Loan Amount
  • Annual Percentage Rate (APR)
  • Length of Loan in Months
  • Loan Payment Formula
  • Interest Payment Formula
  • Date Math Routine
Ask ====> Are there enough inputs to create that output?

Answer: Yes

That is Gunny's simple Gump Proof way of gathering requirements. Here is a Microsoft SQL Server version of the stored procedure. Next time I will show you my working but lame attempt at creating the same amortization stored procedure using ElevateDB. Enjoy!


----------------------------------------------------------------
-- SQL Amortization Schedule
-- Copyright 2011 © By Michael J. Riley
-- www.zilchworks.com
----------------------------------------------------------------
CREATE PROCEDURE [dbo].[spAmortizationSchedule] 

----------------------------------------------------------------
-- INPUT PARAMETERS WITH DEFAULT VALUES
----------------------------------------------------------------
@StartDate  datetime      = '2011-09-15' ,
@Principal  decimal(19,2) = 195000       ,
@APR        decimal(19,4) = 3.25         ,
@Months     integer       = 180

AS

----------------------------------------------------------------
-- VARIABLE DECLARATIONS USED FOR PROCESSING
----------------------------------------------------------------
DECLARE @Payment      decimal(19,2)
DECLARE @PaymentLast  decimal(19,2)
DECLARE @PmtNumber    int
DECLARE @PmtDate      date
DECLARE @BalanceStart decimal(19,2)
DECLARE @PmtInterest  decimal(19,2)
DECLARE @PmtPrincipal decimal(19,2)
DECLARE @BalanceEnd   decimal(19,2)

----------------------------------------------------------------
-- TEMP TABLE TO HOLD AMORTIZATION OUTPUT
----------------------------------------------------------------
CREATE TABLE #TempAmortization
(
PmtNumber    int           , 
PmtDate      date          , 
PmtAmount    decimal(19,2) , 
BalanceStart decimal(19,2) ,
PmtPrincipal decimal(19,2) ,
PmtInterest  decimal(19,2) ,
BalanceEnd   decimal(19,2) 
)

----------------------------------------------------------------
-- CALCULATE MONTHLY PAYMENT BASED ON INPUT PARAMETERS
-- This line may wrap and be hard to read
----------------------------------------------------------------
SET @Payment = ROUND(@Principal * ((@APR/1200)/(1- EXP((@Months*-1) *
LOG(1 + (@APR/1200))))),2)

----------------------------------------------------------------
-- INITALIZE VARIABLES BEFORE THE LOOP STARTS
----------------------------------------------------------------
SET @PmtNumber  = 0
SET @BalanceEnd = @Principal

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR ALL BUT LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
WHILE @PmtNumber < @Months -1
BEGIN
 
 SET @PmtNumber    = @PmtNumber +1
 SET @BalanceStart = @BalanceEnd
 SET @PmtDate      = DATEADD(m, @PmtNumber-1, @StartDate)
 SET @PmtInterest  = ROUND(@BalanceStart *(@APR/1200) ,2)
 SET @PmtPrincipal = @Payment - @PmtInterest
 SET @BalanceEnd   = @BalanceStart - @PmtPrincipal

 INSERT INTO #TempAmortization
 (
 PmtNumber     , 
 PmtDate       , 
 PmtAmount     , 
 BalanceStart  ,
 PmtPrincipal  ,
 PmtInterest   ,
 BalanceEnd    
 )
 VALUES
 (
 @PmtNumber     , 
 @PmtDate       ,  
 @Payment       ,
 @BalanceStart  ,
 @PmtPrincipal  ,
 @PmtInterest   ,
 @BalanceEnd    
 )
 
END

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR THE LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
----------------------------------------------------------------
SET @PmtNumber    = @PmtNumber +1
SET @BalanceStart = @BalanceEnd
SET @PmtDate      = DATEADD(m, @PmtNumber-1, @StartDate )
SET @PmtInterest  = ROUND(@BalanceStart *(@APR/1200) ,2)
SET @PaymentLast  = @BalanceStart + @PmtInterest
SET @PmtPrincipal = @BalanceStart
SET @BalanceEnd   = @BalanceStart + @PmtInterest - @PaymentLast

INSERT INTO #TempAmortization
(
PmtNumber     , 
PmtDate       , 
PmtAmount     , 
BalanceStart  ,
PmtPrincipal  ,
PmtInterest   ,
BalanceEnd    
)
VALUES
(
@PmtNumber     , 
@PmtDate       ,  
@PaymentLast   ,
@BalanceStart  ,
@PmtPrincipal  ,
@PmtInterest   ,
@BalanceEnd    
)

----------------------------------------------------------------
-- RETURN RESULTS FROM TEMPORARY TABLE
----------------------------------------------------------------
SELECT  
PmtNumber     , 
PmtDate       , 
PmtAmount     , 
BalanceStart  ,
PmtPrincipal  ,
PmtInterest   ,
BalanceEnd    
FROM #TempAmortization

----------------------------------------------------------------
-- HOUSEKEEPING
----------------------------------------------------------------
DROP TABLE #TempAmortization
Semper Fi, Gunny Mike

< Prev

Next >