[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

using to the nth power in server 2008

how would you write this formula in sql
This is what i have
@FinalAmount = (@P * (1 + dbo.[IntRate](@N))^@N)

This is the error
Msg 402, Level 16, State 1, Procedure FinalAmount, Line 7
The data types decimal and int are incompatible in the '^' operator.

What I am trying to do is calculate the compound interest
0
jrb47
Asked:
jrb47
  • 9
  • 8
1 Solution
 
chapmandewCommented:
DEcLARE @x INT
SET @FinalAmount = (@P * (1 + dbo.[IntRate](@N))
SET CAST(@FinalAmount AS INT) ^ @N
0
 
jrb47Author Commented:
that does not work
here is my full code

 Function [dbo].[FinalAmount](@P int, @N int, @DepositType char(6))
RETURNS int
AS
BEGIN
DECLARE @FinalAmount int
IF @DepositType = 'saving' set @FinalAmount = (@P+(@N * @P * dbo.[IntRate](@N)))
ELSE set @FinalAmount = (@P * (1 + dbo.[IntRate](@N)^@N * @N))
RETURN @FinalAmount
END

this is the error
Msg 402, Level 16, State 1, Procedure FinalAmount, Line 7
The data types decimal and int are incompatible in the '^' operator.

The ^ is an XOR operator and we are trying to do an exponential operator

0
 
chapmandewCommented:
crap...I knew that.  One second.  
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
chapmandewCommented:
The function is POWER(number, exponent)
0
 
chapmandewCommented:
so, for your first example it would be:

@FinalAmount = POWER (@P * (1 + dbo.[IntRate](@N)), @N)
0
 
jrb47Author Commented:
when i go to call it in this select statement if gives me an incorrect statement
and it give the scientific notation as answer
4.48634164428711E+18

i need it to give me the final amount of money

0
 
chapmandewCommented:
Ok, so give me the value from this statement:  @P * (1 + dbo.[IntRate](@N))

and the @N Value, and I'll see if I can get it to work.  What value do you expect?
0
 
jrb47Author Commented:
p = 5000
rate = 7.5%
years = 5

final amount should equal 7178.15
0
 
chapmandewCommented:
hmmm....what is the mathematical formula again (not in TSQL terms, just math notation)
0
 
jrb47Author Commented:
A = P(1+R)^N
a = total amount
p = principle
r = rate
n = years
0
 
chapmandewCommented:
so....

5000(1.075)^5 is supposed to equate to 7178.15?
0
 
jrb47Author Commented:
yes
0
 
chapmandewCommented:
5000 * 1.075 = 5375.000

5375.000 ^ 5 = 4486341644287109600.000
0
 
jrb47Author Commented:
that is wrong!
the first one is correct

this is to calculate compound interest
0
 
jrb47Author Commented:
here is the correct formula!
@p*(POWER((1+dbo.[IntRate](@N)),@N))
0
 
jrb47Author Commented:
@p*(POWER((1+dbo.[IntRate](@N)),@N))
0
 
chapmandewCommented:
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now