Round Up/Down to the Nearest 5, 10, 100 in SQL

Round Up/Down to the Nearest 5, 10, 100 in SQL

Tudip

24 June 2016

In an application you need to round up numbers to nearest 5 or nearest 10 all the time. For example, you might need to display 1.44 as 1.45 and 1.89 as 1.90. This can be done at either the coding end or at the Database end itself. Here is a small function that you can add in your database  to achieve this result. [Note:- Add your database name at place of “DatabaseName”] and run it.

USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— ===================================================================================
— Author: Tudip Technology
— Create date: 14 May, 2012
— Description: Sql server function to round up/down to nearest dollar, nearest 5/10 dollar,
— nearest 100/200/500/1000 or any
— ===================================================================================
CREATE FUNCTION [dbo].[GetRoundingValue]
(
@InputValue As Float,
@RoundingType As Varchar(30)
)
RETURNS Float
AS
BEGIN
Return Case @RoundingType
When ‘No’ Then
@InputValue
When ‘Up’ Then
CEILING (@InputValue/1)
When ‘Down’ Then
FLOOR (@InputValue/1)
When ‘Nearest5’ Then
ROUND (@InputValue/5,2)*5
When ‘Nearest5Up’ Then
CEILING (@InputValue * 20)/20
When ‘Nearest5Down’ Then
FLOOR (@InputValue * 20)/20
When ‘Nearest10’ Then
ROUND (@InputValue/10,2)*10
When ‘Nearest10Up’ Then
CEILING (@InputValue * 10)/10
When ‘Nearest10Down’ Then
FLOOR (@InputValue * 10)/10
When ‘Nearest100’ Then
ROUND (@InputValue/100,0) * 100
When ‘Nearest100Up’ Then
CEILING (@InputValue / 100) * 100
When ‘Nearest100Down’ Then
FLOOR (@InputValue / 100) * 100
When ‘Nearest200’ Then
ROUND (@InputValue/200,0) * 200
When ‘Nearest200Up’ Then
CEILING (@InputValue / 200) * 200
When ‘Nearest200Down’ Then
FLOOR (@InputValue / 200) * 200
When ‘Nearest500’ Then
ROUND (@InputValue/500,0) * 500
When ‘Nearest500Up’ Then
CEILING (@InputValue / 500) * 500
When ‘Nearest500Down’ Then
FLOOR (@InputValue / 500) * 500
When ‘Nearest1000’ Then
ROUND (@InputValue/1000,0) * 1000
When ‘Nearest1000Up’ Then
CEILING (@InputValue / 1000) * 1000
When ‘Nearest1000Down’ Then
FLOOR (@InputValue / 1000) * 1000
Else @InputValue
End
END

You can test it by following sql scripts

Declare @Amount as float = 11.23
— You can also test by 411.77 5671.99 to know how its work
Select dbo.GetRoundingValue(@Amount,’No’)
Select dbo.GetRoundingValue(@Amount,’Up’)
Select dbo.GetRoundingValue(@Amount,’Down’)
Select dbo.GetRoundingValue(@Amount,’Nearest5′)
Select dbo.GetRoundingValue(@Amount,’Nearest5Up’)
Select dbo.GetRoundingValue(@Amount,’Nearest5Down’)
Select dbo.GetRoundingValue(@Amount,’Nearest10′)
Select dbo.GetRoundingValue(@Amount,’Nearest10Up’)
Select dbo.GetRoundingValue(@Amount,’Nearest10Down’)
Select dbo.GetRoundingValue(@Amount,’Nearest100′)
Select dbo.GetRoundingValue(@Amount,’Nearest100Up’)
Select dbo.GetRoundingValue(@Amount,’Nearest100Down’)
Select dbo.GetRoundingValue(@Amount,’Nearest200′)
Select dbo.GetRoundingValue(@Amount,’Nearest200Up’)
Select dbo.GetRoundingValue(@Amount,’Nearest200Down’)
Select dbo.GetRoundingValue(@Amount,’Nearest500′)
Select dbo.GetRoundingValue(@Amount,’Nearest500Up’)
Select dbo.GetRoundingValue(@Amount,’Nearest500Down’)
Select dbo.GetRoundingValue(@Amount,’Nearest1000′)
Select dbo.GetRoundingValue(@Amount,’Nearest1000Up’)
Select dbo.GetRoundingValue(@Amount,’Nearest1000Down’)

search

Blog Categories

Request a quote