Solved

round up a decimal number

Posted on 2009-04-06
4
1,464 Views
Last Modified: 2012-08-13
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
Comment
Question by:ggilal
4 Comments
 
LVL 22

Expert Comment

by:pivar
Comment Utility
Hi,

Try

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

/peter
0
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
Comment Utility
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

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
please try
Cast(ceiling( cast(TotalRows as decimal(10,2))/@PageSize) as int) as TotPages

Open in new window

0
 
LVL 4

Expert Comment

by:bleach77
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 43
Need Counts 11 40
C# Application Local DB Connection String 23 57
SQL Inner Join Vs SubQueries 9 23
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now