SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Scalar Function and Rounding
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLJames
Starting Member

30 Posts

Posted - 05/20/2013 :  15:13:24  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/20/2013 :  16:08:22  Show Profile  Reply with Quote
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...

Edited by - MuMu88 on 05/20/2013 16:37:46
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/20/2013 :  17:49:42  Show Profile  Reply with Quote
Here is a function that does what you want:


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');

Edited by - MuMu88 on 05/21/2013 11:29:31
Go to Top of Page

SQLJames
Starting Member

30 Posts

Posted - 05/20/2013 :  18:22:13  Show Profile  Reply with Quote
Thank you so much for your time and help! I really appreciate it!
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/20/2013 :  18:28:27  Show Profile  Reply with Quote
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.

Edited by - MuMu88 on 05/20/2013 22:53:31
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000