?
Solved

Rounding Up

Posted on 2014-10-30
4
Medium Priority
?
96 Views
Last Modified: 2014-11-24
Hi

SQL 2008

I am trying to round up a division I am doing so  as follows

For example I have data for a duration of 740 secs, I want to convert this into minutes (740/60).
The answer would be 12.33 (displayed in SQL as 12). I want to round this up to the next whole number which would be 13. IF it was 720 secs that would stay at 12

How can this be done

Thanks
0
Comment
Question by:halifaxman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40413343
What's the logic for rounding up vs. down?  Does 12.01 round up to 13 or stay at 12?
So far..
Declare @numerator int = 740, @denominator int = 60

SELECT CAST(@numerator as decimal(9,2)) / @denominator as decimal_value

-- If 12.01 rounds up to 13
SELECT CASE 
   WHEN @numerator / @denominator = CAST(@numerator as decimal(9,2)) / @denominator THEN @numerator / @denominator
   ELSE @numerator / @denominator + 1 END as integer_value

Open in new window

0
 
LVL 35

Expert Comment

by:ste5an
ID: 40413393
12,33 are 12 hours and 20 minutes. Why rounding to 13?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40413924
The ceiling function should do what you want.
ceiling(740/60) => 13
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40415191
is 1 second = to 1 minute?

select
      secs
    , case when secs % 60 = 0 then secs / 60 else (secs / 60) + 1 end
from table1

Open in new window


| SECS | COLUMN_1 |
|------|----------|
|    1 |        1 |
|    2 |        1 |
|    3 |        1 |
..
|   59 |        1 |
|   60 |        1 |
|   61 |        2 |
...
|  739 |       13 |
|  740 |       13 |
|  741 |       13 |
--

Open in new window

see http://sqlfiddle.com/#!3/19a0a/2
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 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