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.
| Author |
Topic |
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
 Posted - 2004-02-17 : 10:44:38
|
| Hi,I need to round Values in a certain way.The ROUND function in SQL Server doesn't do what I want.I have written a Function That works, but for the sake of Learning I would like to ask if anyone else knows a better way to do this.I need to round values off to the nearest 50 cents.eg 123.25 Should be 123.50 123.74 Should be 123.50 123.75 Should be 124.00 124.24 Should be 124.00 My Function is listed below:--****************************************************************CREATE FUNCTION Round5Bob(@InpValue DECIMAL(18, 2)) RETURNS DECIMAL(18, 2)ASBEGIN DECLARE @TempInt INT DECLARE @CentDiff DECIMAL(18, 2) DECLARE @ReturnVal DECIMAL(18, 2) DECLARE @RoundNum DECIMAL(18, 2) SET @TempInt = CONVERT(INT, @InpValue) SET @CentDiff = @InpValue - Convert(DECIMAL(18, 2), @TempInt) IF @CentDiff < 0.25 BEGIN SET @RoundNum = 0 END IF @CentDiff > 0.24 AND @CentDiff < 0.75 BEGIN SET @RoundNum = 0.5 END IF @CentDiff > 0.74 BEGIN SET @RoundNum = 1 END SET @ReturnVal = @TempInt + @RoundNum RETURN @ReturnValEND--********************************************************************I will only be back at this forum again tommorow if you need any other questions answered.Regards,Duane |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-17 : 11:38:13
|
| [code]CREATE FUNCTION dbo.udf_Round5Bob(@InpValue DECIMAL(18, 2)) RETURNS DECIMAL(18, 2)ASBEGINRETURN CASE WHEN (@InpValue - CONVERT(INT,@InpValue)) < .25 THEN CONVERT(INT,@InpValue) WHEN .25 <= (@InpValue - CONVERT(INT,@InpValue)) AND (@InpValue - CONVERT(INT,@InpValue)) < .75 THEN CONVERT(INT,@InpValue) + .5 WHEN .75 <= (@InpValue - CONVERT(INT,@InpValue)) THEN CONVERT(INT,@InpValue) + 1 ENDEND[/code] |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-17 : 11:41:22
|
| round(convert(decimal(19,2),@InpValue)*2,0)/2==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-17 : 11:46:34
|
quote:
Originally posted by nr
round(convert(decimal(19,2),@InpValue)*2,0)/2==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Nice!! |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-02-17 : 13:24:12
|
Very Nice.  Thankyou very much nr and ehorn.Duane |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-02-18 : 00:07:36
|
| Brilliant, Nigel!!OS |
|
|
|
|
|
|
|
|
Rounding - Anyone know an easier way than this?