Messy Function - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Messy Function

Author

Topic

pinkfloyd43
Starting Member

5 Posts

Posted - 2009-02-09 : 18:55:12

Trying to duplicate some vb.net code in sql, not much fun so far. I have to have it for some charting I am doing and need the information summed up on the database. I have attempted to create a function that mimics the vb code and believe I have datatypes incorrect or ??

Here is my function, be gentle I am far from expert! All the commented code below the function is what I am trying to reproduce in this function, basically trying to modify some times.
The bolded area is where I think I am sucking! The function takes a single parameter, example I was using was 95, the VB code return 1.75 and the paramter is in seconds.

Probably an easy issue for someone who is more versed in this stuff as I am certainly more comfortable creating front ends! Thanks!

ALTER FUNCTION [dbo].[fnGetRoundedLODAmount]
(
@ai_value int
)
RETURNS int
AS

BEGIN
DECLARE @Results int
DECLARE @WholeNum int
DECLARE @Remainder int
DECLARE @Double decimal

SET @WholeNum = @ai_value/60
SET @Remainder = @ai_value%60
SET @Double =@Remainder/60

IF @Double > 0.75 AND @ai_Value >=0
SET @WholeNum = @WholeNum + 1
ELSE IF
@Double > 0.75 AND @ai_Value < 0
SET @WholeNum = @WholeNum -1
ELSE IF
@Double > 0.5
SET @Double = 0.75
ELSE IF
@Double > 0.25
SET @Double = 0.5
ELSE IF
@Double > 0
SET @Double = 0.25

-- Set final results
IF @ai_value >= 0
BEGIN
SET @Results = @WholeNum + @Double
END
ELSE
BEGIN
SET @Results = @WholeNum - @Double
END

-- Return results
RETURN @Results
END

-- Private Function CalculateNumber(ByVal num As Integer) As Double
-- Dim li_WholeNum As Integer, li_Remainder As Integer, ld_Double As Double
--
-- li_WholeNum = Math.DivRem(num, 60, li_Remainder)
--
-- ld_Double = Math.Round(Math.Abs(li_Remainder) / 60, 2)
--
-- Select Case True
-- Case ld_Double > 0.75
-- If Math.Sign(num) >= 0 Then
-- li_WholeNum += 1
-- Else
-- li_WholeNum -= 1
-- End If
--
-- ld_Double = 0
-- Case ld_Double > 0.5
-- ld_Double = 0.75
-- Case ld_Double > 0.25
-- ld_Double = 0.5
-- Case ld_Double > 0
-- ld_Double = 0.25
-- End Select
--
-- If Math.Sign(num) >= 0 Then
-- Return Convert.ToDouble(li_WholeNum + ld_Double)
-- Else
-- Return Convert.ToDouble(li_WholeNum - ld_Double)
-- End If
-- End Function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 23:00:47

[code]ALTER FUNCTION [dbo].[fnGetRoundedLODAmount]
(
@ai_value int
)
RETURNS int
AS

BEGIN
DECLARE @Results int
DECLARE @WholeNum int
DECLARE @Remainder int
DECLARE @Double decimal

SET @WholeNum = @ai_value/60
SET @Remainder = @ai_value%60
SET @Double =@Remainder/60

SET @WholeNum = CASE WHEN @Double > 0.75 AND @ai_Value >=0 THEN @WholeNum + 1
WHEN @Double > 0.75 AND @ai_Value < 0 THEN @WholeNum -1
END,
@Double= CASE WHEN @Double > 0.5 THEN 0.75
WHEN @Double > 0.25 THEN 0.5
WHEN @Double > 0 THEN 0.25
END,
@Results = CASE WHEN @ai_value >= 0 THEN @WholeNum + @Double
ELSE @WholeNum - @Double
END

-- Return results
RETURN @Results
END[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 03:00:37

Since the function returns an INT, the decimals will be lost!
Try this

ALTER FUNCTION [dbo].[fnGetRoundedLODAmount]
(
	@ai_value INT
)
RETURNS MONEY
AS
BEGIN
	RETURN	CASE
			WHEN @ai_value % 60 = 0 THEN @ai_value
			ELSE 15 + @ai_value - @ai_value % 15
		END / 60.0E
END

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 10:45:39

yup... nice catch

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 10:51:56

I think my INLINE version is smoother too.
And will perform a lot faster!

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources