Solved

How do I round up to the nearest 100??

Posted on 2010-01-05
428 Views
I'm using SQL to run some very in depth estimating formulas and need to be able to Round Up within the statement.  For instance, if the value my formula shoots out is 7122, I want that to be 7200.

Round(7122,-2)  gives me 7100
Ceiling(7122) gives me 7122 (too bad ceiling doesn't take 2 arguements).

Any ideas?
0
Question by:drunk_irishman

LVL 26

Accepted Solution

use this

CEILING(7122 / 100.0) * 100
0

LVL 75

Expert Comment

declare @x int
set @x = 7100
SELECT case when @x%100 > 0 THEN  ((@x/100)+1 )*100 ELSE (@x/100)*100 end
0

LVL 1

Author Comment

clever, thank you sir.
0

LVL 5

Expert Comment

use this
i had to use a dummy table just ignore it
CEILING returns the smallest integer which is equal to or greater than the number it is given
the result on my example is the following
note just use ceiling(NUMBER/100)*100 in the example i show you all the results
``````select top 1 7122

, 7122/100.0               as dividedby100

, ceiling(7122/100.0)      as ceilingresult

, ceiling(7122/100.0)*100 as myfinalnumber

from gl20000
``````
ceiling.JPG
0

Featured Post

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…