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

vjhs03f7 于 2022-12-22 发布在其他

关注(0)|答案(1)|浏览(123)

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

sql-server

来源：https://stackoverflow.com/questions/74708973/how-can-i-execute-a-custom-round-in-sql-by-0-25-increments

1条答案

按热度按时间

eyh26e7m1#

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

赞(0）回复(0）举报 2022-12-22

我来回答

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

1条答案

相关问题

热门标签

最新问答