Solved

# round up a decimal number

Posted on 2009-04-06
1,474 Views
I have this line in my SP:
Cast(ceiling((TotalRows/@PageSize)) as int) as TotPages

I am using CTE and  ROW_NUMBER()OVER

when the TotalRows = 282 and @PageSize = 60 I am receiving TotPages = 4 while I really need 5 as the result.

How can this be done?
Thanks
0
Question by:ggilal
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 22

Expert Comment

ID: 24075166
Hi,

Try

Cast(ceiling((cast(TotalRows as decimal(6)/@PageSize)) as int) as TotPages

/peter
0

LVL 23

Accepted Solution

ID: 24075169
Hi,

You need to cast TotalRows and @PageSize to decimals to ensure degree of accuracy you require to then round up:

``````SELECT CAST(CEILING(CAST(TotalRows AS DECIMAL)/CAST(@PageSize AS DECIMAL)) AS INTEGER) as TotPages
``````
0

LVL 143

Expert Comment

ID: 24075175
``````Cast(ceiling( cast(TotalRows as decimal(10,2))/@PageSize) as int) as TotPages
``````
0

LVL 4

Expert Comment

ID: 24075293
In MSSQL, when you divide an integer with an integer, the result will be an integer. So in your case, 282/60 will be 4 instead of 4.7. So ceiling(4) will return 4. Just cast the variable with float or decimal or any like. it should work.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month2 days, 22 hours left to enroll