Solved

Rounding digit to specific range

Posted on 2013-11-22
5
182 Views
Last Modified: 2013-11-23
Needed help for SQL select statement!

For an example I want to round
95.1556 to 95.125
or
5.625 to 5.625

in my Excel query I use Floor(95.1556,0.125) and I got 95.125 but don't know how to do it in SQL statement in SQL 2008 database
0
Comment
Question by:eli411
  • 2
  • 2
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39669941
Since you're talking eighths, an easy way would be to multiply the number by eight, then round, then divide by eight.
Declare @n numeric(19,4) = 95.1556
SELECT ROUND(@n * 8,0) / 8

SET @n = 5.625
SELECT ROUND(@n * 8,0) / 8

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39669981
And what would you want for 95.1897? 95.125 or 95.250?
0
 
LVL 2

Author Comment

by:eli411
ID: 39670249
awking00, thanks for the syntaxes...

Here is my select statement

select MortgageRatePricing
from dbo.MortgageRate

and the output shown as follow

95.1556
96.3024

and I wanted to round them to 0.125 which will be

95.125
96.250

can you show that in the syntax using your example?  Thank you!
0
 
LVL 2

Author Comment

by:eli411
ID: 39670267
never mind!  Got it to work!  Here is the final syntax just for someone needed in the future.

select round(cast(MortgageRatePricing as numeric(19,4))*8,0)/8 as MortgageRatePricing
from dbo.MortgageRate
0
 
LVL 32

Expert Comment

by:awking00
ID: 39671475
The reason I asked my question was that I wasn't sure if you wanted the decimal places "truncated" to the eighths or if it should be rounded up to the next eighth if called for. The examples you've provided work for both since they "truncate" to the previous eighth and round down to the previous eighth. However, 95.1897 would "truncate" to 95.125, but would round to 95.250 (95.1897 times 8 = 761.5176 which rounds to 762 and divided by 8 = 95.250).
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

947 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

22 Experts available now in Live!

Get 1:1 Help Now