How to SELECT round up number in SQL Server – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server, case. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have a salary table with this column
EMPLOYEE_NAME SALARY
------------------------
ANNA 113750
MARRY 124300
BELLA 105100
I want to round up the amount of salary with 2000 or 5000 fractions which is nearby. So for this case it will be like
EMPLOYEE_NAME SALARY
------------------------
ANNA 114000
MARRY 125000
BELLA 106000
For additional information, I used MS SQL Server. Please help me to do that. Thanks in advance
Solution :
Divide the salary by the fraction. Use ceiling to round up. Then multiply by the fraction.
Declare @salary table (Employee_name nvarchar(50), Salary money)
Declare @fraction money = 5000
insert into @salary
values
('ANNA', 113750),
('MARRY', 124300),
('BELLA', 105100)
update @salary
set Salary = ceiling(salary/@fraction)*@fraction
select * from @salary
Unlike ROUND (which rounds up and down) the CELING function (which you want to round up) has no length parameter (as in ROUND(salary, -3)). So divide and multiply to get the desired result:
select employee_name, ceiling(salary / 1000.0) * 1000.0 as salary
from mytable;
CEILINGis documented here: https://msdn.microsoft.com/de-de/library/ms189818.aspxROUNDis documented here: https://msdn.microsoft.com/de-de/library/ms175003.aspx
Try below CEILING method :
DECLARE @fraction MONEY = 1000
SELECT CEILING(113750/@fraction)*@fraction
SELECT CEILING(124300/@fraction)*@fraction
SELECT CEILING(105100/@fraction)*@fraction
You can use the CEILING function.
Like:
SELECT EMPLOYEE_NAME CEILING(SALARY)
Short summary from the above link:
In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value that is greater than or equal to a number.
try this.
select EMPLOYEE_NAME, ceiling(salary / 1000.0) * 1000.0 as SALARY from table;
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.