1

I have a list of values in SQL Server which need some customized rounding. I have tried with inbuilt SQL Server functions but those functions didn't work.

I want to round a decimal value to nearest 5 value

For example

  • 1.02,1.01,1.03,1.04,1.05 -- should be rounded to 1.05
  • 1.06,1.07,1.08,1.09,1.10 -- should be rounded to 1.10
  • 1.11,1.12,1.13,1.14,1.15 -- should be rounded to 1.15
  • 1.16,1.17,1.18,1.19,1.20 -- should be rounded to 1.20

Could you please suggest a working solution for this scenario in SQL Server?

Thanks in advance

Raju

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
VRaju
  • 21
  • 1
  • 8

1 Answers1

0

Are you only ever going to deal with two decimal digits?

If so, you could use a function like this:

CREATE FUNCTION dbo.RoundToFive(@Input DECIMAL(16,2))
RETURNS DECIMAL(16,2)
AS
BEGIN
    RETURN ROUND(@Input*20 + 0.49, 0) / 20.0;
END;

If you use this like this:

DECLARE @data TABLE (StartValue DECIMAL(16,2))

INSERT INTO @data (StartValue) 
VALUES (1.00), (1.02), (1.01), (1.03), (1.04), (1.05), (1.06)

SELECT StartValue, dbo.RoundToFive(StartValue)
FROM @data

you'll get an output like this:

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks Much Marc_S, This solutions seems to be working :). Could you please provide some simple explanation about this part ""ROUND(@Input*20 + 0.49, 0) / 20.0 "" it would be helpful for me and others as well :) – VRaju Aug 22 '20 at 10:35
  • @VRaju: the `ROUND(... * 20;0) / 20.0` approach basically rounds any amount to an accuracy of `0.05` (e.g. 5 cents in a currency). But that's not what you want - you have a slightly skewed rounding - 1.01 and 1.02 need to be rounded up to 1.05 - and that's what the `+ 0.49` part achieves – marc_s Aug 22 '20 at 11:06
  • @VRaju: If you feel this answer helped you solve your problem, then please [**accept this answer**](http://meta.stackoverflow.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Aug 23 '20 at 15:35