Rounding rounding decimal values by 0.25

• affu1100

  SSC Veteran

  Points: 234

  More actions

  May 23, 2011 at 11:29 pm

  #240970

  Hi ,

  I'm trying to round up my decimal values by 0.25 ,

  for Example if the value is 2.20 then it should show 2.25

  Similarly,

  2.60 To 2.75

  2.80 To 3.00

  2.10 To 2.25

  Can any body help me on this..., I'm Badly Stuck...

• Ryan007

  SSCrazy Eights

  Points: 8275

  More actions

  May 24, 2011 at 2:44 am

  #1328879

  declare @a decimal(18,2)

  set @a=18.40

  if @a>round(@a,0)

  Begin

  if @a-round(@a,0)<=.25

  Begin

  select @a=round(@a,0)+.25

  goto Value

  end

  else

  Begin

  --select round(@a,0)

  select @a=round(@a,0)+.5

  goto Value

  end

  end

  Else

  Begin

  if round(@a,0)-@a=.25 or round(@a,0)-@a=.50

  begin

  set @a = @a

  goto Value

  end

  If round(@a,0)-@a>.25

  Begin

  set @a=round(@a,0)-.25

  goto Value

  end

  else

  begin

  set @a=round(@a,0)

  goto Value

  End

  end

  value:

  print @a

  Ryan
  //All our dreams can come true, if we have the courage to pursue them//

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 24, 2011 at 3:21 am

  #1328887

  You can divide by 0.25 and use CEILING to round up:

  ;WITH Numbers (N) AS (

  SELECT 2.20

  UNION ALL

  SELECT 2.60

  UNION ALL

  SELECT 2.80

  UNION ALL

  SELECT 2.10

  )

  SELECT CEILING(N / 0.25) * 0.25

  FROM Numbers

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• Lowell

  SSC Guru

  Points: 323546

  More actions

  May 24, 2011 at 6:19 am

  #1328984

  Nice code Gianluca;

  I see how if you are rounding up, it works perfectly; I added that to my snippets for future use, thanks.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 24, 2011 at 6:25 am

  #1328988

  Lowell (5/24/2011)

  ---

  Nice code Gianluca;

  I see how if you are rounding up, it works perfectly; I added that to my snippets for future use, thanks.

  Thanks Lowell, glad you like it. 😉

  -- Gianluca Sartori