How can I execute a custom round in SQL by 0.25 increments?-CodePudding

I'm trying to create a custom round in SQL that rounds up or down by .25 but I'm not certain if this is actually possible with what I'm trying to accomplish.
I've tried the ROUND statement but it seems you can only specify the number of decimal places you round the number to...I could be wrong here but looking for some guidance.

The rounding should follow the rules below:

First check:

If value after the decimal is between .00 and .25:
Round up to .25 if value after the decimal is greater than or equal to 0.125
Round down to .00 if value after the decimal is less than 0.125
If value after the decimal is between .25 and .50:
Round up to .50 if value after the decimal is greater than or equal to 0.375
Round down to .25 if value after the decimal is less than 0.375
If value after the decimal is between .50 and .75:
Round up to .75 if value after the decimal is greater than or equal to 0.625
Round down to .50 if value is less than 0.625
If value after the decimal is between .75 and .99:
Round up to the whole number if value after the decimal is greater than or equal to 0.875
Round down to .75 if value after the decimal is less than 0.875

So all values will either be:
X.00
X.25
X.50
X.75

For example,
4.416 should round up to 4.50
2.75 remains 2.75
5.834 should round down to 5.75
7.083 should round down to 7.00

CodePudding user response：

With just a little math... The convert(decimal(10,2),...) is optional

Declare @YourTable Table ([SomeCol] decimal(10,4))  Insert Into @YourTable Values 
 (4.416)
,(2.75)
,(5.834)
,(7.083)
 
Select *
      ,NewValue = convert(decimal(10,2),round(SomeCol/.25,0)*.25)
 From @YourTable

Results

SomeCol NewValue
4.4160  4.50
2.7500  2.75
5.8340  5.75
7.0830  7.00