• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1494
  • Last Modified:

round up a decimal number

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
ggilal
Asked:
ggilal
1 Solution
 
pivarCommented:
Hi,

Try

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

/peter
0
 
adatheladCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try
Cast(ceiling( cast(TotalRows as decimal(10,2))/@PageSize) as int) as TotPages

Open in new window

0
 
bleach77Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now