interest rate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

interest rate

Author

Topic

suki_66
Starting Member

12 Posts

Posted - 2010-03-29 : 11:40:11

I am trying to create a procedure to calculate the interest rate - i am new at this so am having trouble getting started - here is what i have so far:
Cust table
CustID CustName PrincipleAmount NumberOfYears DepositType

I have created a function that sets the principle rate

CREATE FUNCTION [dbo].[IntRate](@NumberofYears decimal(7,4))
RETURNS decimal(7,4)
AS
BEGIN
DECLARE @rate decimal(7,4)
if @NumberofYears>=20 set @rate =.15
else if @NumberofYears >= 11 set @rate =.125
else if @NumberofYears>= 6 set @rate =.10
else if @NumberofYears>= 4 set @rate =.075
else if @NumberofYears>= 1 set @rate =.05
else set @rate = 0
RETURN @rate
END

I want to use the following formula:

SELECT
case DepositType
when 'saving' then NumberOfYears * PrincipleAmount * [dbo].[IntRate](NumberofYears)AS rate
else PrincipleAmount * (1 + [ dbo].[IntRate](NumberofYears)AS rate) * NumberOfYears
end

FROM Customers

PROBLEM
I get this error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.

Can someone please tell me what i am doing wrong.

FROM Customers
When I do this I get this error:

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-29 : 11:47:19

try this:


SELECT
	case DepositType
		when 'saving' then NumberOfYears * PrincipleAmount * [dbo].[IntRate](NumberofYears)
else PrincipleAmount * (1 + [ dbo].[IntRate](NumberofYears)) * NumberOfYears
end AS Rate

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

suki_66
Starting Member

12 Posts

Posted - 2010-03-29 : 11:55:01

oops, Actually I need to create this as a stored procedure SO here is what i have

CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount(real)
IF @DepositType = 'saving' set @FinalAmount = (NumberOfYears * PrincipleAmount * [dbo].[IntRate](NumberofYears)AS rate)
ELSE set @FinalAmount = PrincipleAmount * (1 + [ dbo].[IntRate](NumberofYears)AS rate) * NumberOfYears
RETURN @FinalAmount
END

These are the errors I am getting

Msg 102, Level 15, State 1, Procedure FinalAmount, Line 5
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Procedure FinalAmount, Line 6
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure FinalAmount, Line 7
Incorrect syntax near the keyword 'AS'.
Msg 137, Level 15, State 2, Procedure FinalAmount, Line 8
Must declare the scalar variable "@FinalAmount".

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-29 : 12:02:57

What is [dbo].[IntRate](NumberofYears)?


CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount real

Select @FinalAmount =
case when @DepositType = 'saving' then (NumberOfYears * PrincipleAmount * [dbo].[IntRate](NumberofYears))
else PrincipleAmount * (1 + [ dbo].[IntRate](NumberofYears)) * NumberOfYears end
from Customers

RETURN @FinalAmount
END

Harsh Athalye
http://www.letsgeek.net/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:41:58

seems like another scalar valued udf

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suki_66
Starting Member

12 Posts

Posted - 2010-03-29 : 12:45:50

That is a function that was created to determine the rate of interest used based on how many years the money has been deposited

so using select would work better than an if/else?

also when i try to view the results - it does not work using
SELECT CustID, CustName, NumberofYears, dbo.FinalAmount(DepositType) AS FinalAmount
FROM Customers

Msg 4121, Level 16, State 1, Line 2
Cannot find either column " dbo" or the user-defined function or aggregate " dbo.IntRate", or the name is ambiguous.

so i am thinking it is formatted incorrectly.....I do have the function IntRate listed in this thread

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:56:16

try dbo.[IntRate] instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suki_66
Starting Member

12 Posts

Posted - 2010-03-29 : 13:00:18

quote:
Originally posted by visakh16

try dbo.[IntRate] instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

this is the error i get now
Msg 4104, Level 16, State 1, Procedure FinalAmount, Line 8
The multi-part identifier "dbo.IntRate" could not be bound.
Msg 4104, Level 16, State 1, Procedure FinalAmount, Line 9
The multi-part identifier " dbo.IntRate" could not be bound.

this is the revised code
CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount real
DECLARE @NumberOFYears int
DECLARE @PrincipleAmount float
IF @DepositType = 'saving' set @FinalAmount = (@NumberOfYears * @PrincipleAmount * [dbo].[IntRate])
ELSE set @FinalAmount = (@PrincipleAmount * (1 + ([ dbo].[IntRate]) * @NumberOfYears))
RETURN @FinalAmount
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 13:03:17

have you created the intrate udf already?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suki_66
Starting Member

12 Posts

Posted - 2010-03-29 : 13:05:42

quote:
Originally posted by visakh16

have you created the intrate udf already?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yes i have already created it.....and it is a scalar UDF

here is the intRate code


CREATE FUNCTION  [dbo].[IntRate](@NumberofYears decimal(7,4))
	RETURNS decimal(7,4)
AS
BEGIN
	DECLARE @rate decimal(7,4)
	if @NumberofYears>=20 set @rate =.15
	else if @NumberofYears >= 11 set @rate =.125
	else if @NumberofYears>= 6 set @rate =.10
	else if @NumberofYears>= 4 set @rate =.075
	else if @NumberofYears>= 1 set @rate =.05
	else set @rate = 0
	RETURN @rate
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 13:08:49

try


CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount real
DECLARE @NumberOFYears int
DECLARE @PrincipleAmount float
IF @DepositType = 'saving' select @FinalAmount = (@NumberOfYears * @PrincipleAmount * [dbo].[IntRate])
ELSE select @FinalAmount = (@PrincipleAmount * (1 + ([dbo].[IntRate]) * @NumberOfYears))
RETURN @FinalAmount
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suki_66
Starting Member

12 Posts

Posted - 2010-03-30 : 00:02:37

I finally figured it out - thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 14:27:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources