This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

I have a table with the the following columns

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 @NumberofYears = '0'

RETURN @rate

END

no i need to be able to calculate the final amount taking variable from both of these tables

PROBLEM:

I am unclear how to call the variable from the function

problem trying to solve:

Final Amount (A) – Takes three inputs P, N, and Deposit Type and returns the final amount using either simple interest formula or compound interest formula, depending upon the deposit type. For the calculation of R it uses the rate of interest function.

formulas: A=PNR (for simple interest)

A=P(1+r)^N (for compound interest)

so i know i need an if statement based on the deposittype

I need to know how to get started or how to call the rate from the function.

Thanks

CustID CustName PrincipleAmount NumberOfYears DepositType

I have created a function that sets the principle rate

CREATE FUNCTION [dbo].[IntRate](@NumberofY

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 @NumberofYears = '0'

RETURN @rate

END

no i need to be able to calculate the final amount taking variable from both of these tables

PROBLEM:

I am unclear how to call the variable from the function

problem trying to solve:

Final Amount (A) – Takes three inputs P, N, and Deposit Type and returns the final amount using either simple interest formula or compound interest formula, depending upon the deposit type. For the calculation of R it uses the rate of interest function.

formulas: A=PNR (for simple interest)

A=P(1+r)^N (for compound interest)

so i know i need an if statement based on the deposittype

I need to know how to get started or how to call the rate from the function.

Thanks

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialalso the table can be use more eaily for historical reporting etc...

CREATE Function FinalAmount(@DepositType char(6))

RETURNS real

AS

BEGIN

DECLARE @FinalAmount(real)

IF @DepositType = 'saving' set @FinalAmount = (NumberOfYears * PrincipleAmount * [dbo].[IntRate](NumberofYe

ELSE set @FinalAmount = PrincipleAmount * (1 + [ dbo].[IntRate](NumberofYea

RETURN @FinalAmount

END

AND these are the errors it generates

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".

I am not sure what i am doing wrong

I thought that i did declare the scalar variable

```
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
```

is this homework?

you haven;t declare the number of years variable

and as it says get rid of the as clause

here is the revision

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](NumberofY

ELSE set @FinalAmount = (@PrincipleAmount * (1 + ([ dbo].[IntRate](NumberofYea

RETURN @FinalAmount

END

here is the error

Msg 207, Level 16, State 1, Procedure FinalAmount, Line 8

Invalid column name 'NumberofYears'.

the intRate is another UDF that I am trying to call -

subsequent errors

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.

```
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
```

CREATE Function [dbo].[FinalAmount](@P float, @N int, @DepositType char(6))

RETURNS float

AS

BEGIN

DECLARE @FinalAmount float

IF @DepositType = 'saving' set @FinalAmount = (@P+(@N * @P * dbo.[IntRate](@N)))

IF @DepositType = 'fixed' set @FinalAmount = @p*(POWER((1+dbo.[IntRate]

RETURN @FinalAmount

END

plus the question was homework/coursework related.

I pay for this service so that i can get HELP in understanding how to do some of the coursework - I never make any bones about what I am working on a problem for. The agreement says the experts will not do the work FOR you - I did not ask that, in fact I posted the solution with little to no help from this person. I will close and award the points based on the answer which shows the format for using the first UDF in a call.

If I need to be the one to change it let me know.

I think you misunderstand my comments as harsh. It was merely an explanation as to why I am here - I am very aware that this is a volunteer service and that while I can ask I am not guaranteed an answer. However, you will note that once the question of if it was homework came up - I was not longer helped. I worked through the solution by doing more research.

I am okay with that.

The question was

I am unclear how to call the variable from the function

The answer is

CREATE Function [dbo].[FinalAmount](@P float, @N int, @DepositType char(6))

with the [dbo].FinalAmount] being how the udf should be referenced

and this dbo.[IntRate](@N)) being how it was passed -

I gave the formula that I new I would need and plainly stated that I new I would have to use an IF statement....

This done so that I could give the areas I was comfortable with

I am not saying that the person did not help - they did

Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

? else set @rate = 0

and you shouldn't have quotes around the rates

LIKE

SELECT case ratetype when 'S' then numberofyears * AMT * [dbo].[IntRate](NumberofYe

else amt * (1 + [ dbo].[IntRate](NumberofYea

end

FROM TABLE...

BUT really you should have an interest rate table which would hold the correct rate

e.g.

loanname, period, rate