ElevateDB: Stored Procedures Part 5

In the last post of this series ElevateDB: Stored Procedures Part 4 I told you I would share with you the optimized version of the stored procedure written by Tim Young from ElevateSoft and I will. However, I need to point out that the monthly payment was calculated differently between the Microsoft SQL code and the ElevateDB code.

I made sure I declared the APR input parameter as decimal(19,4) in both Microsft SQL and ElevateDB. Here are those declarations along with the appropriate code snippets:

-----------------------------------------------------------------
-- Microsoft SQL
-----------------------------------------------------------------
@APR decimal(19,4) = 3.25
DECLARE @Payment decimal(19,2)
SET @Payment = ROUND(@Principal * ((@APR/1200)/(1- EXP((@Months*-1) * LOG(1 + (@APR/1200))))),2)

Payment = 1370.20 (this is correct)

-----------------------------------------------------------------
-- ElevateDB (APR = 3.25 although it's not shown)
-----------------------------------------------------------------
IN "APR" DECIMAL(19,4), 
DECLARE Payment decimal(19,2);
SET Payment = ROUND(Principal * ((APR/1200)/(1- EXP((Months*-1) * LN(1 + (APR/1200))))) to 2);

Payment = 1369.26 (this is incorrect)

So, I decided to investigate why the two payments were different. Upon close examination I found the following:

Even though both database have APR defined as decimal(19,4)...

Microsoft SQL calculates @APR/1200 as 0.002708333
ElevateDB calculates APR/1200 as 0.0027

I won't speculate why Microsoft's calculations seem to ignore the decimal place rules. I do know that ElevateDB does enforce the decimal place rules. So, armed with this bit of information, I changed ElevateDB to use the Float datatype for APR,

-----------------------------------------------------------------
-- ElevateDB Revisited (APR = 3.25 although it's not shown)
-----------------------------------------------------------------
IN "APR" float,
DECLARE Payment decimal(19,2);
SET Payment = ROUND(Principal * ((APR/1200)/(1- EXP((Months*-1) * LN(1 + (APR/1200))))) to 2);

Payment = 1370.20 (this is now correct)

So, if you intend to do calculations where the decimal places need to go beyond 4 places when using ElevateDB make sure to use the float datatype.

As promised, here is the optimized amortization schedule that Tim Young produced from the example Microsoft SQL Code i posted to his forum. There's a lot you can learn about how ElevateDB works from this code snippet, Enjoy!

----------------------------------------------------------------
-- SQL Amortization Schedule
-- Copyright 2011 © By Michael J. Riley
-- www.zilchworks.com
-- Created by Tim Young - ElevateSoft
-- www.elevatesoft.com
----------------------------------------------------------------
CREATE PROCEDURE "spAmortizationSchedule02" (
INOUT "StartDate" DATE, 
INOUT "Principal" DECIMAL(19,4), 
INOUT "APR" FLOAT, 
INOUT "Months" INTEGER)
BEGIN

----------------------------------------------------------------
-- VARIABLE DECLARATIONS USED FOR PROCESSING
----------------------------------------------------------------

DECLARE InsertStmt STATEMENT;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR ResultStmt;

DECLARE Payment      DECIMAL(19,4);
DECLARE PaymentLast  DECIMAL(19,4);
DECLARE PmtNumber    INTEGER;
DECLARE PmtDate      DATE;
DECLARE BalanceStart DECIMAL(19,4);
DECLARE PmtInterest  DECIMAL(19,4);
DECLARE PmtPrincipal DECIMAL(19,4);
DECLARE BalanceEnd   DECIMAL(19,4);

----------------------------------------------------------------
-- INPUT PARAMETERS WITH DEFAULT VALUES
----------------------------------------------------------------

SET StartDate = COALESCE(StartDate, CURRENT_DATE());
SET Principal = COALESCE(Principal, 195000);
SET APR = COALESCE(APR, 3.25);
SET Months = COALESCE(Months, 180);

----------------------------------------------------------------
-- TEMP TABLE TO HOLD AMORTIZATION OUTPUT
----------------------------------------------------------------

PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?';
OPEN InfoCursor USING 'TempAmortization02';
IF ROWCOUNT(InfoCursor) > 0 THEN
  EXECUTE IMMEDIATE 'EMPTY TABLE TempAmortization02';
ELSE
  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE TempAmortization02
                    (
                    PmtNumber    INTEGER       ,
                    PmtDate      DATE          ,
                    PmtAmount    DECIMAL(19,4) ,
                    BalanceStart DECIMAL(19,4) ,
                    PmtPrincipal DECIMAL(19,4) ,
                    PmtInterest  DECIMAL(19,4) ,
                    BalanceEnd   DECIMAL(19,4)
                    )';
END IF;

----------------------------------------------------------------
-- 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))))) to 2);

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

PREPARE InsertStmt FROM 'INSERT INTO TempAmortization02
                       (
                       PmtNumber     ,
                       PmtDate       ,
                       PmtAmount     ,
                       BalanceStart  ,
                       PmtPrincipal  ,
                       PmtInterest   ,
                       BalanceEnd
                       )
                       VALUES (?,?,?,?,?,?,?)';

----------------------------------------------------------------
-- PERFORM CALCULATIONS FOR ALL BUT LAST MONTH AND
-- STORE RESULTS IN TEMPORARY TABLE
-- 
-- MICROSOFT SQL DATEADD(,,
-- ELEVATEDB SQL  + INTERVAL '' 
----------------------------------------------------------------
WHILE PmtNumber < Months -1 DO

SET PmtNumber    = PmtNumber + 1;
SET BalanceStart = BalanceEnd;
SET PmtDate      = (StartDate + CAST(PmtNumber-1 AS INTERVAL MONTH));
SET PmtInterest  = ROUND(BalanceStart *(APR/1200) to 2);
SET PmtPrincipal = Payment - PmtInterest;
SET BalanceEnd   = BalanceStart - PmtPrincipal;

EXECUTE InsertStmt USING PmtNumber     ,
                         PmtDate       ,
                         Payment       ,
                         BalanceStart  ,
                         PmtPrincipal  ,
                         PmtInterest   ,
                         BalanceEnd;

END WHILE;

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

EXECUTE InsertStmt USING PmtNumber     ,
                        PmtDate       ,
                        PaymentLast   ,
                        BalanceStart  ,
                        PmtPrincipal  ,
                        PmtInterest   ,
                        BalanceEnd;

----------------------------------------------------------------
-- RETURN RESULTS FROM TEMPORARY TABLE
----------------------------------------------------------------

PREPARE ResultStmt FROM 'SELECT
                       PmtNumber     ,
                       PmtDate       ,
                       PmtAmount     ,
                       BalanceStart  ,
                       PmtPrincipal  ,
                       PmtInterest   ,
                       BalanceEnd
                       FROM TempAmortization02';
OPEN ResultCursor;

END

VERSION 1.00!
----------------------------------------------------------------

Semper Fi,
Gunny Mike

< Prev