I have developed a database in Access but am now having a problem moving it over to MSSQL. I'm experienced in Access and new to SQL. The problem is with Functions. In my access database I have a table called Mortgage_Rates. What I have done is create a qurey that will generate the APR based upon the rate.

Here is my SQL Statement from Access.

SELECT
Product,
Purch_Ref_Rate,
-PMT(Purch_Ref_Rate/12,180,100000,0,0) AS Payment,
Rate(180,Payment,-(100000-2500),0)*12 AS APR,
FROM
Mortgage_Rates;

This works great in Access however when I create the View in SQL I get an Error saying that the PMT Function is not a valid function. I did a little digging in the Help files and found it was valid in something called SQL Server Analysis Services. I installed it and took a look at it. I am not sure if this is what I was looking for.

So the question is,
Do anyone know how to make my (Access) SQL Statement work in MSSQL(2000 pro). Or Do I have to use this Analysis Services thing. Or am I completely missing the mark altogether.

I don't know anything about what PMT does, but it appears to be an Excel function. I'm not sure how you call it in Access, unless it's also a standard Access function or it's been implemented in Access (in a module or vba behind a form) by creating a new Excel object and calling the Excel function. In any case, to use it SQL you'll need to create a new User-defined function to do the calculation (which means knowing what's involved in the PMT calculation). Once you've set up the function, it's called just like you call PMT in your query above.

As far as I'm aware, analysis services is used for anyalysing your database in real-time, and it doesn't make any difference to what's available through standard queries, so that won't help you.

HTH

Flynnious

0

gumball_chCommented:

Hi

can you post the result of this select statement, than we can analyze what this function is for.

Note you need SQL 2000 or above to have it work
if you have SQL 7, just take the SQL and paste it in your code

create function pmt (@MonthlyRate float, @NumberOfMonths int, @Amount float)
returns float
as
begin
return (@amount*@monthlyrate/(1-power(1+@monthlyrate,-1*@numberofmonths)))
end

Hilaire

0

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

the rate calculation is more complicated
and involves an iterative process

of course this could be done in T-SQL in a user-defined function, but I'll have to go through the code to pick up the algorithm and I don't have enough time for it just now

I'd tend to say that if the algorithm happens to be too complex, it could be a good idea to use an external component (active X dll) to perform this task

Maybe you could close this question and open a new one,
cause this rate() issue sounds much more complicated, and maybe other experts will come with better ideas

This kind of function should work,
I haven't tested it yet, not even checked syntax because I don't have a SQL box available at the moment

create function rate (@NumberOfMonths int, @Payment float, @Amount float)
returns float
as
begin
declare @Payment_calc float, @rate_max float, @rate_min float, @rate float, @iter_cnt int
set @rate_max=0.2
set @rate=0.1
set @rate_min=0.0
set @iter_cnt=0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
while ((abs(@Payment-@Payment_calc ) > 0.1) and (@iter_cnt < 256))
begin
if (@Payment>@Payment_calc ) set @rate_min=@rate else set @rate_max = @rate
set @rate = (@rate_min + @rate_max)/2.0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
set @iter_cnt = @iter_cnt + 1
end
return (@rate)
end

iteration count is to avoid endless loop
I'll check the function tomorrow, you can give it a try if you want

I just had the chance to test it and it seems to work reasonabily fast.
The algo is maybe not perfect but it's the best I could do at 1:00 AM
gives the same results than Excel

XL : rate(120,-2000,80000) = 2,3451688%

my function : rate(120,2000,80000) = 0.0234527587890625

(note there's no minus sign)

if you need more precision, you'll have to change
((abs(@Payment-@Payment_calc ) > 0.1) in the while loop
into
((abs(@Payment-@Payment_calc ) > 0.05)
lower values => higher precision, more iterations

I let you see what you need

For the sake of clarity, I didn't double-check the input parameters (amount positive, <> 0, ...) but can do it if you want

Sorry for the delay in response.
I was off of work on thurs and fri.
However, I just tried your function for rate, it worked great. I do appreciate all of the work you did on this.

If you don't mind revealing your secrets, may I asked where you found the infromation for how to write these functions,

I am new to sql and I am looking to build my resources a bit. If you know of a few good resource sites, fourms I would be greatful.

Before I close this question, I need to make sure that this rate passes with the higher ups in the Company. I am almost certain we only have to go 5 Decimal places on our equation. Once I know for sure all of my questions will be answerd 100% and the points are yours.

But to help build ego, I must admint I now consider you a genius. thanks for your help.

Strictly speaking, mine was the first correct answer, because I identified that the functions are available in Access and not in SQL, and you'd have to write a User-defined function to implement them yourself. All I didn't do was give you the code for the functions :-D

I wouldn't dare be so audacious as to say Hilaire doesn't deserve the points. All the points should go to Hilaire for quite extraordinary amounts of effort. I just wanted to point out my answer was right, and useful, and I'm going to take a small amount of deflected credit for that. Hope you don't mind :o)

I've got no secrets, and google is my best friend.
My best site to learn new tips is EE
The good thing in EE is that for someone eager to learn like me, it's always real-world issues, not ugly exercices to counts cats and mice

For the formulas, I didn't find really intersting links apart from the two urls I gave in a previous post.
From the comments on the MS article, I wrote my own algorithm based on a natural method, exactly the same as in the game
"guess a number between 0 and 100"
first you answer 50, to get rid of half of the possible values in the current range, and so on ...
I think it's called a dichotomic decision tree and allows you to find a value whithout beeing really able to calculate it

Well, as luck would have it, I need to go one more decimal. I did increase (@Payment-@Payment_calc ) > 0.1 to .05 but I am still having a problem with accuracy.

My Excel results. My SQL Results What I need
0.05260076170689670000 0.052587890625 .052600
0.05230183997979690000 0.052294921875 .052301
0.05201446376607380000 0.052001953125 .052014

I need my results to be consistent to 6 Decimal places. Any idea what type of change I should do?

Hi, this new version uses decimal data types instead of float
This gives the ability to truncate the results to any number of digits accurately

(there are values that can't be rounded properly using float datatype)

Also changed 0.05 to 0.001 for more precision

Hilaire

create function rate (@NumberOfMonths int, @Payment decimal(15,7), @Amount decimal(15,7))
returns decimal(15,6)
as
begin
declare @Payment_calc decimal(15,7), @rate_max decimal(15,7), @rate_min decimal(15,7), @rate decimal(15,7), @iter_cnt int
set @rate_max=0.2
set @rate=0.1
set @rate_min=0.0
set @iter_cnt=0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
while ((abs(@Payment-@Payment_calc ) > 0.001) and (@iter_cnt < 256))
begin
if (@Payment>@Payment_calc ) set @rate_min=@rate else set @rate_max = @rate
set @rate = (@rate_min + @rate_max)/2.0
set @Payment_calc = (@amount*@rate/(1-power(1+@rate,-1*@numberofmonths)))
set @iter_cnt = @iter_cnt + 1
end
return round(@rate, 6)
end

0

Featured Post

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

As far as I'm aware, analysis services is used for anyalysing your database in real-time, and it doesn't make any difference to what's available through standard queries, so that won't help you.

HTH

Flynnious