Solved

Rounding Up

Posted on 2014-10-30
4
92 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
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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 33

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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

789 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