MS-SQL Sprocs - looping,case,ou tput parameters and 12 cents

[Lyle Fairfield]

The stored procedure script below is an example of how looping, case
statements and output parameters can be used in MS-SQL stored
procedures to accomplish things for which we may have had to use VBA
code, or automation, in the JET world. Some who have not yet worked with
MS-SQL may be interested.
The script is indented in reality but I have aligned it all left to make
reading easier, (I hope).

The Sproc takes an Account ID, an Interest Rate, and a Date, and
calculates the monthly interest for the month of the date, taking into
account daily transactions if any, and summing the daily interest
calculated at (1+apr^(1/365))-1 ... sometimes 366. It calculates the
interest for each day of the month (including the interest on the
interest) and adds them up, all within MS-SQL. It's quite fast, doing a
few dozen accounts in a couple of seconds.

But I do not post only to be informative. I post because the value the
Sproc returns is slightly off the value returned when I try to run an
identical model in Excel, or when I try to run an identical model in
ASP-JScript. For instance Excel and ASP report January's interest for
Account 21 is $536.35; this MS-SQL Sproc says $536.23.

The difference of $0.12 is not a problem in that I could decide to use
one or the other method and as long as I am consistent in doing so, this
is acceptable.

The crux of the problem is that I am the youngest of 3 siblings and must
be perfect. And this is not perfect. As I have three methods and two
give me the same answer I am assuming the third (this one) is in error.

But where? I have checked all the type declarations. They seem to be
consistent. I have checked the days scanned. They seem to be identical.
I know MS-SQL can be very idiosyncratic (read nuts) in its type
coercions but I can't see any where that this could be a problem as I
have for the most part made explicit conversions.

Can you see a problem?

ALTER PROCEDURE sp4060148Intere st
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInte rest smallmoney OUTPUT,
@GetAccountName varchar(250) OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestR ate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReci procal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInteres t float
DECLARE @Today smalldatetime

SET @EndDate=DATEAD D(year,1,@Begin Date)
SET @DaysinYear=DAT EDIFF(dayofyear ,@BeginDate,@En dDate)
SET @APRPlusOne=@AP R+1
SET @DaysinYearReci procal=1.000000 0000/@DaysinYear
SET @DailyInterestR ate=POWER(@APRP lusOne,@DaysinY earReciprocal)-1
SET @Today=DATEADD( dayofyear,-DAY(@BeginDate) +1,@BeginDate)
SET @MonthlyInteres t=0

WHILE MONTH(@Today) = MONTH(@BeginDat e) AND YEAR(@Today)=YE AR(@BeginDate)
BEGIN
SELECT
@MonthlyInteres t=@MonthlyInter est+(SUM(sq.Amo unt)+@MonthlyIn terest)*@DailyI nterestRate
FROM
(
SELECT
CASE WHEN t.DebitAccountI D=@AccountID THEN -t.TotalAmount ELSE
t.TotalAmount END AS Amount
FROM
[4060148Transact ions] t
WHERE
(
t.DebitAccountI D=@AccountID
OR
t.CreditAccount ID=@AccountID
)
AND
t.Date<@Today
)
AS
sq

SET @Today=DATEADD( day,1,@Today)
CONTINUE
END
SET @GetMonthlyInte rest = CAST(@MonthlyIn terest AS smallmoney)
RETURN

(Yes I know I have a redundant Parameter but it is not doing any harm
and I have someone travelling in Europe right now who feeds this Sproc 5
parameters and I think I will wait until I can change that before I
remove the extra parameter.)

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Terry Kreft]

Lyle,
Try replacing your floats with decimals

--
Terry Kreft
MVP Microsoft Access
"Lyle Fairfield" <ly******@yahoo .ca> wrote in message
news:2p******** ********@read1. cgocable.net...
<SNIP>

Sproc returns is slightly off the value returned when I try to run an
identical model in Excel, or when I try to run an identical model in
ASP-JScript. For instance Excel and ASP report January's interest for
Account 21 is $536.35; this MS-SQL Sproc says $536.23.

The difference of $0.12 is not a problem in that I could decide to use
one or the other method and as long as I am consistent in doing so, this
is acceptable. <SNIP>
--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Lyle Fairfield]

Terry Kreft wrote:

Lyle,
Try replacing your floats with decimals

Thanks for looking at this, Terry. I believe Excel uses the IEEE Double
Precision 64 bit representation of numbers by default.

To approximate this in T-SQL I have tried both
float
and
decimal(38,18)

Unfortunately, it seems that the outcome of the calculation of interest
is identical, regardless of which I use.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[billmiami2]

Take a look at your calculation of the daily interest rate. I would
think that it would simply be

@APR*@DaysinYea rReciprocal

Which is mathematically

r/n

where r is the annual rate and n is the number of periods (365 or 366).

You are showing it as

[1+r]^(1/n)

I'm not familiar with this expression.

If I use a .05% APR with your method, I get an effective daily interest
rate of

0.000133681

which is not the same as

0.000136986 which is what I think you should have with daily compound
interest at 5%.

Bill E.
Hollywood

[Lyle Fairfield]

bi********@nets cape.net wrote:

Take a look at your calculation of the daily interest rate. I would
think that it would simply be

@APR*@DaysinYea rReciprocal

Which is mathematically

r/n

where r is the annual rate and n is the number of periods (365 or 366).

You are showing it as

[1+r]^(1/n)

I'm not familiar with this expression.

If I use a .05% APR with your method, I get an effective daily interest
rate of

0.000133681

which is not the same as

0.000136986 which is what I think you should have with daily compound
interest at 5%.

Bill E.
Hollywood

Suppose the APR is 8%

0.08/365 =0.000219178082 191780821917808 21917808

If you apply this and compound it every day for a year the effective APR is

1.0002191780821 917808219178082 1917808 ^ 365

=

1.0832775717928 069729659276566 459.

which is nice work if you can get it, but it is not an APR of 8%.

The formula I gave is correct except in the world of unscrupulous and
dishonest business, which is close to but not all the world of business.
--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[billmiami2]

Lyle,

That is precisely the outcome you should have for interest compounded
daily at and 8% annual rate -- you should get more than 8%. This
converges to

e^(rt)=1.083287 ...

or about 8.329% as you approach instantaneous (or continuous)
compounding.

Perhaps APR is interpreted differently from the "r" that I'm
accustomed to seeing in a mortgage calculation. Perhaps it's the rate
at which one would end up with an 8% return when daily compounding is
applied, which would be the equivalent of an "r" of about 7.696%. If
that's true, then the APR is a "watered down" interpretation of an
annual interest rate. If the bank calculated my mortgage interest that
way, I'd save some money.

Bill

[Lyle Fairfield]

bi********@nets cape.net wrote:

Lyle,

That is precisely the outcome you should have for interest compounded
daily at and 8% annual rate -- you should get more than 8%. This
converges to

e^(rt)=1.083287 ...

or about 8.329% as you approach instantaneous (or continuous)
compounding.

Perhaps APR is interpreted differently from the "r" that I'm
accustomed to seeing in a mortgage calculation. Perhaps it's the rate
at which one would end up with an 8% return when daily compounding is
applied, which would be the equivalent of an "r" of about 7.696%. If
that's true, then the APR is a "watered down" interpretation of an
annual interest rate. If the bank calculated my mortgage interest that
way, I'd save some money.

Yes, if banks were honest we would all save money.

If I lend you money at a rate of 8% a year, then, after a year you will
pay, or have paid me (or owe even) 8%.
I am not a bank. I am honest. Sorry, I see that I have repeated myself.

The Canadian Housing and Mortgage Corporation used to, and perhaps still
does, require that mortgages be compounded semianually, (even when
payments were made monthly), to minimize the effect of the simple
calculation of interest as you describe it.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[billmiami2]

Lyle,

I'm not sure I consider this dishonestly. Perhaps it's because I've
been taught that compounding gives you an effective interest rate
that's higher than the stated annual rate. Therefore, when a bank
charges me a 6% rate for a loan, I expect to be paying an effective
rate (or APR, I suppose) of about 6.17% due to monthly compounding. I
guess in Canada, you would get the same loan for the bargain price of
only 6.09%!

I made two tiny changes to your procedure to specify
@GetMonthlyInte rest and @MonthlyInteres t as a decimal types and was
able to match results in MS Excel with excellent precision. Note that
I replaced your SQL statement with a fixed value of $100.00. I placed
my code below. Also, I think that you could throw away the iterative
approach altogether by calculating the future value of the incremental
daily cash flows and summing them. The first cash flow would be the
balance in the account at the beginning of day 1 of the month, for
which interest would be compounded at your daily rate for the number of
days in the month. The second cash flow would be the total additional
debits/credits made on day 1, compounded at your daily rate for the
number of days in the month minus one, and so on.

Bill E.

_____________
Declare @MonthlyInteres t decimal(11,10)
exec sp4060148Intere st 0, .08, '2/1/2005', @MonthlyInteres t output
Print Cast(@MonthlyIn terest as varchar)

-----------

ALTER PROCEDURE sp4060148Intere st
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInte rest Decimal(16,10) OUTPUT--,
--@GetAccountName varchar(250) OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestR ate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReci procal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInteres t decimal(16,10)
DECLARE @Today smalldatetime

SET @EndDate=DATEAD D(year,1,@Begin Date)
SET @DaysinYear=DAT EDIFF(dayofyear ,@BeginDate,@En dDate)
SET @APRPlusOne=@AP R+1
SET @DaysinYearReci procal=1.000000 0000/@DaysinYear
SET @DailyInterestR ate=POWER(@APRP lusOne,@DaysinY earReciprocal)-1
SET @Today=DATEADD( dayofyear,-DAY(@BeginDate) +1,@BeginDate)
SET @MonthlyInteres t=0

WHILE MONTH(@Today) = MONTH(@BeginDat e) AND
YEAR(@Today)=YE AR(@BeginDate)
BEGIN
SELECT
@MonthlyInteres t=@MonthlyInter est+(100.000000 00+@MonthlyInte rest)*@DailyInt erestRate
--Print cast(@MonthlyIn terest as varchar)

SET @Today=DATEADD( day,1,@Today)
CONTINUE
END
Declare @reaTemp real
Set @GetMonthlyInte rest =@MonthlyIntere st
RETURN

[Lyle Fairfield]

bi********@nets cape.net wrote:

Also, I think that you could throw away the iterative
approach altogether by calculating the future value of the incremental
daily cash flows and summing them.
I would be interested in learning more about how you would do this.

***********
The first cash flow would be the
balance in the account at the beginning of day 1 of the month, for
which interest would be compounded at your daily rate for the number of
days in the month.

This was the basis for my error. In entering last month's interest I was
using the first day of this month as the date. This meant that interest
was not calculated properly for that day. When I changed this date to
the last day of the month for which the interest was calculated, the
correct answer was returned.

***********

Thanks for your interest and help with this matter, Bill. As a result of
working with the problem I think I have a more efficient SPROC (as
below), but I'd like to see how you would apply future value to the
question.

********** revised SPROC **********

ALTER PROCEDURE sp4060148Intere st
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInte rest smallmoney OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestR ate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReci procal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInteres t float
DECLARE @Today smalldatetime

SET @EndDate=DATEAD D(year,1,@Begin Date)
SET @DaysinYear=DAT EDIFF(dayofyear ,@BeginDate,@En dDate)
SET @APRPlusOne=@AP R+1
SET @DaysinYearReci procal=1.000000 0000/@DaysinYear
SET @DailyInterestR ate=POWER(@APRP lusOne,@DaysinY earReciprocal)-1
SET @Today=@BeginDa te
SET @MonthlyInteres t=0

WHILE MONTH(@Today) = MONTH(@BeginDat e) AND YEAR(@Today)=YE AR(@BeginDate)
Begin
SELECT
@MonthlyInteres t=@MonthlyInter est+(sq.Amount+ @MonthlyInteres t)*@DailyIntere stRate
FROM
(
SELECT SUM(CASE WHEN t.DebitAccountI D=@AccountID THEN -t.TotalAmount
ELSE t.TotalAmount END) AS Amount
FROM [4060148Transact ions] t
WHERE (t.DebitAccount ID=@AccountID OR t.CreditAccount ID=@AccountID)
AND t.[Date]<@Today
)
sq
SET @Today=DATEADD( day,1,@Today)
CONTINUE
End
SET @GetMonthlyInte rest = CAST(@MonthlyIn terest AS smallmoney)
Return


--
--
Lyle

[billmiami2]

Lyle,

Here is an example for you using a cash flow method. Because I wasn't
sure what your transaction table contained, I created my own
transaction table.

My transaction table is as follows:

----------------------------------------------------
CREATE TABLE [tblTransactions] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [int] NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[Amount] [money] NOT NULL ,
CONSTRAINT [PK_tblTransacti ons] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY]
) ON [PRIMARY]
----------------------------------------------------
Note that the TransactionID column is completely unnecessary but I put
it in out of habit.

My sample data for this table is as follows:
-----------------------------------------------------

ID Account Date Amount
1 1 12/4/2004 50
2 1 12/30/2004 50
3 1 1/10/2005 2:00:00 PM 100
4 1 1/10/2005 3:00:00 PM -50
5 1 1/25/2005 7:00:00 AM 100
6 1 2/1/2005 6:00:00 PM 50
-----------------------------------------------------

The table valued function fnDateRangeToTa ble makes it easy to calculate
daily cash flows for all days of the month in one query without
iteration. The function itself contains a loop, but there is almost no
cost to this. It is as follows:
---------------------------------------------
CREATE FUNCTION dbo.fnDateRange ToTable
--Loop through a range of dates to create a table
(
@dtsStartDate smalldatetime,
@dtsEndDate smalldatetime
)
RETURNS @tblResult TABLE (Date smalldatetime)
AS
BEGIN
Declare @dtsDate smalldatetime
set @dtsDate=@dtsSt artDate

While @dtsDate<=@dtsE ndDate
Begin
--Insert the new record
INSERT INTO @tblResult SELECT @dtsDate
--increment the date
Set @dtsDate=datead d(dd,1,@dtsDate )
End

RETURN
END

---------------------------------------------
And finally, my procedure is as follows:

---------------------------------------------

CREATE PROCEDURE dbo.spCalculate InterestForMont h
--Use cash flow method to calculate interest
@AccountID int=1,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInte rest float output
AS
set nocount on

--Declare variables
Declare @FirstDayMonth smalldatetime
Declare @FirstDayNextMo nth smalldatetime
Declare @LastDayMonth smalldatetime
Declare @DaysInMonth smallint
Declare @APRPlusOne float
Declare @DailyInterestR atePlusOne float
Declare @DaysinYear smallint
Declare @DaysinYearReci procal float
Declare @EndDate smalldatetime
Declare @MonthlyInteres t float

Set @EndDate=DATEAD D(year,1,@Begin Date)
Set @DaysinYear=DAT EDIFF(dayofyear ,@BeginDate,@En dDate)
Set @APRPlusOne=@AP R+1
Set @DaysinYearReci procal=1.000000 0000/@DaysinYear
Set @DailyInterestR atePlusOne=POWE R(@APRPlusOne,@ DaysinYearRecip rocal)
Set @FirstDayMonth= DATEADD(dayofye ar,-DAY(@BeginDate) +1,@BeginDate)
Set @FirstDayNextMo nth=DateAdd(m,1 ,@FirstDayMonth )
Set @LastDayMonth=D ateAdd(d, -1, @FirstDayNextMo nth)
Set @DaysInMonth=Da teDiff(d, @FirstDayMonth, @FirstDayNextMo nth)
Set @MonthlyInteres t=0

--Calculate the future value of the balance as of the beginning of the
first day of the month
--less the nominal value to get the interest on the beginning balance.
SELECT
@MonthlyInteres t=Sum(Amount)*( Power(@DailyInt erestRatePlusOn e,@DaysInMonth)-1)
FROM tblTransactions
WHERE Date < @FirstDayMonth

--Sum the future values of the incremental daily cash flows and
subtract the nominal value
--to get the interest on the incremental flows. Add this to the
interest on the beginning balance.
SELECT
@MonthlyInteres t=@MonthlyInter est+Sum(FV.Futu reValue)-Sum(FV.CashFlow )
FROM
--subquery to calculate future values for daily incremental cash
flows
(SELECT CF.Date, CF.CashFlow,
CF.CashFlow*Pow er(@DailyIntere stRatePlusOne,@ DaysInMonth+1-Day(CF.Date))
AS FutureValue
FROM
--subquery to get daily incremental cash flows
(SELECT D.Date, Sum(Amount) AS CashFlow
FROM tblTransactions AS T INNER JOIN
dbo.fnDateRange ToTable(@FirstD ayMonth, @LastDayMonth) AS D
ON T.Date >= D.Date AND T.Date < DateAdd(d,1,D.D ate)
WHERE AccountID=@Acco untID
GROUP BY D.Date) AS CF) AS FV

Set @GetMonthlyInte rest = @MonthlyInteres t
RETURN
--------------------------------------------------
Testing the procedure for January, 2005 at an 8% APR gives me
Declare @Interest float
exec spCalculateInte restForMonth 1, .08, '2/1/2005', @Interest output
select @Interest as Result

Which returns 1.0359642798191 26

If you take my three transactions for January and set their amounts to
zero, you get

0.6557826187859 6056

Which is what you should earn in interest on a $100 balance as of
1/1/05 compounded over 31 days.

Bill