Mathematical Computation in Transact SQL

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

General SQL Server Forums

New to SQL Server Programming

Mathematical Computation in Transact SQL

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-03-05 : 15:28:48

What is the equivalent in Transact SQL for VB.net ^ (Exponent Operator)

I have a following equation in VB.net
DotGain = ((1 - 10 ^(D0 - D50)) /(1 - 10 ^ (D0 - D100))) * 100 - 50
where D0, D50 and D100 are decimal variables

Working on creating a SQL UDF where @D0,@D50 and @D100 are transact sql decimal variables

DECLARE @DotGain decimal
DECLARE @D0 decimal
DECLARE @D50 decimal
DECLARE @D100 decimal

SET @D0 = 0.10
SET @D50 = 0.54
SET @D100 = 1.20

SELECT @DotGain = 1 - POWER(10,(@D0 - @D50)) / 1 - POWER(10,(@D0 - @D100)) * 100 - 50

but it is giving me different result in TSQL than VB2005

Can someone correct me?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-05 : 15:41:31

You should provide sizes (precision and scale) for your decimal data types.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-03-05 : 15:47:09

Tara, I am getting -49 in transact sql compared to 19.2 in VB.net

this is the VB.net code which gives correct result
Public Class Form1

Private Sub Button1_Click( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim result as Decimal
result = CalcDotGain(0.10,0.54,1.20)
End Sub

Private Function CalcDotGain(ByVal D0 As Decimal, ByVal D50 As Decimal, ByVal D100 As Decimal ) As Decimal

Dim dDotGain As Decimal
'Check for Divide by Zero Error
If (1-10^(D0 - D100) <> 0 ) AND (D50 > 0 ) Then
dDotGain = ( (1 - 10 ^(D0 - D50)) / (1 - 10 ^ (D0 - 100)) ) * 100 - 50 Else
dDotGain = -1.00 'Error
End If
Return dDotGain

End Function
End Class
something wrong with my formula may be i need a cup of coffee

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 15:54:42

Two things: 1 and 10 are not a numeric they are integers and you should use parentheses to ensure order of operations.

DECLARE @DotGain decimal
DECLARE @D0 decimal(18,9)
DECLARE @D50 decimal(18,9)
DECLARE @D100 decimal(18,9)

SET @D0 = 0.10
SET @D50 = 0.54
SET @D100 = 1.20


SELECT 1 - POWER(10,(@D0 - @D50)) / 1 - POWER(10,(@D0 - @D100)) * 100 - 50
SELECT ((1.0 - POWER(10.0,(@D0 - @D50))) / (1.0 - POWER(10.0,(@D0 - @D100)))) * 100 - 50

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-03-05 : 16:00:38

Lamprey, I tried to your suggestion but it is now giving me 16.6 instead of 19.2

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 16:04:16

More presision issues, try this:

DECLARE @DotGain decimal
DECLARE @D0 decimal(18,9)
DECLARE @D50 decimal(18,9)
DECLARE @D100 decimal(18,9)
DECLARE @One DECIMAL (18,9)
DECLARE @Ten DECIMAL (18,9)

SET @D0 = 0.10
SET @D50 = 0.54
SET @D100 = 1.20
SET @One = 1.0
SET @Ten = 10.0

SELECT ((@One - POWER(@Ten,(@D0 - @D50))) / (@One - POWER(@Ten,(@D0 - @D100)))) * 100 - 50

-- Or

SELECT ((CAST(1 AS DECIMAL(18,9)) - POWER(CAST(10 AS DECIMAL(18,9)),(@D0 - @D50))) / (CAST(1 AS DECIMAL(18,9)) - POWER(CAST(10 AS DECIMAL(18,9)),(@D0 - @D100)))) * 100 - 50

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-03-05 : 16:06:25

I tried it with decimal(6,2) but i think i need more precision as you have suggested. Well learn something new today.

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 16:07:39

No Problem. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources