Convert Scalar Function and Rounding

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

Convert Scalar Function and Rounding

Author

Topic

SQLJames
Starting Member

35 Posts

Posted - 2013-05-20 : 15:13:24

I was asked to convert a function from Java to SQL. I know that I am lucky. The Java function is called on Sybase from within a Stored Procedure. I know this is not a Java forum (or Sybase for that matter) but I am interested in getting the parts for SQL Server working. I understand most of this but was wondering a about one part.

Here is the function in Java:

public class Growth
{

public Growth()
{
}

public static BigDecimal growth(BigDecimal lastAmt, BigDecimal currAmt)
{
BigDecimal growthPct;
try
{
growthPct = currAmt.subtract(lastAmt).divide(lastAmt.abs(), 3, 4);
}
catch(ArithmeticException ae)
{
growthPct = null;
}
catch(NullPointerException npe)
{
growthPct = null;
}
return growthPct;
}
}

I have no problem doing that as a function in SQL Server. Here is what I would start with for the calculation:

growthPct = ABS((currAmt - lastAmt) / lastAmt);

The Rounding Mode in Java represented by the 4 is HALF_UP (if I got my enum numbering start point correct by counting from zero) Is there a way to implement HALF_UP using SQL Server with rounding?

Thanks in advance for your consideration and time!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-20 : 16:08:22

This may help you:
http://support.microsoft.com/kb/196652
http://docs.oracle.com/javase/6/docs/api/java/math/RoundingMode.html

You can utilize CEILING() and FLOOR() fucntions in SQL Server, but these functions do not give you exactly what you are looking for so you may have to write your own function...

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-20 : 17:49:42

Here is a function that does what you want:
[CODE]

IF OBJECT_ID('dbo.Java_HALF_UP_DOWN') IS NOT NULL DROP FUNCTION dbo.Java_HALF_UP_DOWN;

GO
CREATE FUNCTION dbo.Java_HALF_UP_DOWN
(
@nval AS NUMERIC(12,5),
@type AS VARCHAR(8)
)
RETURNS NUMERIC(12,5)
AS
BEGIN
DECLARE @cval AS NUMERIC(12, 5)
DECLARE @sval AS NUMERIC(2,1)
DECLARE @tval AS INT

SET @sval = CASE WHEN @nval < 0 THEN -1 ELSE 1 END
SET @cval = ABS(@nval) - FLOOR(ABS(@nval))

RETURN (CASE WHEN (@type = 'DOWN') and @cval <= 0.5 THEN FLOOR(ABS(@nval))
WHEN (@type = 'UP') and @cval < 0.5 THEN FLOOR(ABS(@nval))
ELSE CEILING(ABS(@nval)) END) * @sval;
END

GO
SELECT 88887.50001 as val, dbo.Java_HALF_UP_DOWN(88887.50001, 'UP') as Java_Half_UP
UNION ALL
SELECT 88887.49999 as val, dbo.Java_HALF_UP_DOWN(88887.49999, 'UP')
UNION ALL
SELECT 88887.50000 as val, dbo.Java_HALF_UP_DOWN(88887.50000, 'UP')
UNION ALL
SELECT -88887.50000 as val, dbo.Java_HALF_UP_DOWN(-88887.50000, 'UP')
UNION ALL
SELECT 5.5 as val, dbo.Java_HALF_UP_DOWN(5.5, 'UP')
UNION ALL
SELECT 2.5 as val, dbo.Java_HALF_UP_DOWN(2.5, 'UP')
UNION ALL
SELECT 1.6 as val, dbo.Java_HALF_UP_DOWN(1.6, 'UP')
UNION ALL
SELECT 1.1 as val, dbo.Java_HALF_UP_DOWN(1.1, 'UP')
UNION ALL
SELECT 1.0 as val, dbo.Java_HALF_UP_DOWN(1.0, 'UP')
UNION ALL
SELECT -1.0 as val, dbo.Java_HALF_UP_DOWN(-1.0, 'UP')
UNION ALL
SELECT -1.1 as val, dbo.Java_HALF_UP_DOWN(-1.1, 'UP')
UNION ALL
SELECT -1.6 as val, dbo.Java_HALF_UP_DOWN(-1.6, 'UP')
UNION ALL
SELECT -2.5 as val, dbo.Java_HALF_UP_DOWN(-2.5, 'UP')
UNION ALL
SELECT -5.5 as val, dbo.Java_HALF_UP_DOWN(-5.5, 'UP');

SELECT 88887.50001 as val, dbo.Java_HALF_UP_DOWN(88887.50001, 'DOWN') as Java_Half_DOWN
UNION ALL
SELECT 88887.49999 as val, dbo.Java_HALF_UP_DOWN(88887.49999, 'DOWN')
UNION ALL
SELECT 88887.50000 as val, dbo.Java_HALF_UP_DOWN(88887.50000, 'DOWN')
UNION ALL
SELECT -88887.50000 as val, dbo.Java_HALF_UP_DOWN(-88887.50000, 'DOWN')
UNION ALL
SELECT -88887.5881 as val, dbo.Java_HALF_UP_DOWN(-88887.5881, 'DOWN')
UNION ALL
SELECT 5.5 as val, dbo.Java_HALF_UP_DOWN(5.5, 'DOWN')
UNION ALL
SELECT 2.5 as val, dbo.Java_HALF_UP_DOWN(2.5, 'DOWN')
UNION ALL
SELECT 1.6 as val, dbo.Java_HALF_UP_DOWN(1.6, 'DOWN')
UNION ALL
SELECT 1.1 as val, dbo.Java_HALF_UP_DOWN(1.1, 'DOWN')
UNION ALL
SELECT 1.0 as val, dbo.Java_HALF_UP_DOWN(1.0, 'DOWN')
UNION ALL
SELECT -1.0 as val, dbo.Java_HALF_UP_DOWN(-1.0, 'DOWN')
UNION ALL
SELECT -1.1 as val, dbo.Java_HALF_UP_DOWN(-1.1, 'DOWN')
UNION ALL
SELECT -1.6 as val, dbo.Java_HALF_UP_DOWN(-1.6, 'DOWN')
UNION ALL
SELECT -2.5 as val, dbo.Java_HALF_UP_DOWN(-2.5, 'DOWN')
UNION ALL
SELECT -5.5 as val, dbo.Java_HALF_UP_DOWN(-5.5, 'DOWN');
[/CODE]

SQLJames
Starting Member

35 Posts

Posted - 2013-05-20 : 18:22:13

Thank you so much for your time and help! I really appreciate it!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-20 : 18:28:27

quote:
Originally posted by SQLJames

Thank you so much for your time and help! I really appreciate it!

Glad to help.

I fixed a small bug in the earlier code. I color coded the changes in red.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources