round and calculate - 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 2000 Forums

SQL Server Development (2000)

round and calculate

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-06-02 : 14:53:06

I am trying to import two functions from MS-ACCESS to sql server 2000. Can anything shed some lights please.....................

create Function CalculateSplit(ByVal idMinutes As Double, ByVal ilCount As Double) As Double
Dim ldMod As Double
Dim ldMinSplit As Double
Dim ldDecSplit As Double
--Debug.Print ldMod
ldMinSplit = idMinutes / ilCount
ldMod = ldMinSplit Mod 15
ldDecSplit = (ldMinSplit - Int(ldMinSplit))
If (ldDecSplit <= 0.5) Then
ldMod = ldMod + ldDecSplit
Else
ldMod = ldMod - 1 + ldDecSplit
End If

--CalculateSplit = Round(((idMinutes / ilCount) + IIf(((idMinutes / ilCount) Mod 15) = 0, 0, (15 - ldMod))) / 60, 2)
CalculateSplit = RoundUp(idMinutes / ilCount, 30)

End Function

CREATE Function RoundUp(ByVal idMinutes As Double, ByVal id As Double) As Double

Dim ldMod As Double
Dim ldMinSplit As Double
Dim ldDecSplit As Double
--Debug.Print ldMod

ldMod = idMinutes Mod id
ldDecSplit = (idMinutes - Int(idMinutes))
If (ldDecSplit <= 0.5) Then
ldMod = ldMod + ldDecSplit
Else
ldMod = ldMod - 1 + ldDecSplit
End If

RoundUp = Round(((idMinutes) + IIf(((idMinutes) Mod id) = 0, 0, (id - ldMod))) / 60, 2)

End Function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 15:05:55

Use CASE WHEN instead of IIF and also use CAST(field as int) instead of int(field). Also syntax of Round is ROUND(field,precision)

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-06-02 : 15:10:08

Thank you Vikas for your input. I am totally new to sql server function. Can you please try to convert one of the function so I can use that as a sample and write the other one. I would truly appreciated it.

I am logged in as "oa". is that same as schema?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 15:17:34

Something like this:-

create Function CalculateSplit
(idMinutes Float, 
ilCount Float) 
RETURNS Float 
AS
BEGIN
DECLARE @ldMod Float,@ldMinSplit Float,@ldDecSplit Float

SET @ldMinSplit = @idMinutes / @ilCount
SET @ldMod = @ldMinSplit % 15
SET @ldDecSplit = @ldMinSplit - CAST(@ldMinSplit AS int)

SET  @ldMod =CASE WHEN @ldDecSplit <= 0.5 THEN @ldMod + @ldDecSplit
                  ELSE @ldMod - 1 + @ldDecSplit
             END
RETURN Round(@idMinutes / @ilCount, 30)
END

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-06-02 : 15:25:07

hi Visakh,

Thank you very very much for your valuable time. I ran the function you supplied.
1. I am receiving following error.
2. Does your function takes care of roundup (the sub function) within.

Thanks
Julia

error
Msg 170, Level 15, State 1, Procedure CalculateSplit, Line 2
Line 2: Incorrect syntax near 'idMinutes'.
Msg 137, Level 15, State 2, Procedure CalculateSplit, Line 9
Must declare the variable '@idMinutes'.
Msg 137, Level 15, State 2, Procedure CalculateSplit, Line 16
Must declare the variable '@idMinutes'.

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-06-02 : 16:22:21

I fixed idmod but function has no error. but I am not able to accomplish what I had planned. Please suggest.

Thanks
Julia

CREATE Function oa.CalculateSplit(@idMinutes Float, @ilCount Float) RETURNS Float
AS
BEGIN
DECLARE @ldMod Float,@ldMinSplit Float,@ldDecSplit Float

SET @ldMinSplit = @idMinutes / @ilCount
SET @ldMod = cast(@ldMinSplit as int)% 15
SET @ldDecSplit = @ldMinSplit - CAST(@ldMinSplit AS int)

SET @ldMod =CASE WHEN @ldDecSplit <= 0.5 THEN @ldMod + @ldDecSplit
ELSE @ldMod - 1 + @ldDecSplit
END
RETURN Round(@idMinutes / @ilCount, 30)
END

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-06-02 : 19:12:00

This code is working..

alter Function oa.CalculateSplit(@idMinutes Float, @idCount Float) RETURNS Float
AS
BEGIN

RETURN oa.Roundup(@idminutes/@idcount)

END

alter Function oa.RoundUp(@idMinutes As float) returns float
as
begin
declare @hours as float
set @hours = CAST(@idminutes AS INT)

IF @idminutes-@hours > 0
if @idminutes-@hours > .5
set @hours = @hours+1
ELSE
SET @HOURS=@HOURS+.5
return @HOURS
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources