Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1487
  • 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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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