Solved

Rounding Up

Posted on 2014-10-30
4
88 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Sums/Grouping 7 51
Square brackets 4 37
xpath sql query 2008 8 41
SQL Error in WHERE Clause 5 37
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short film showing how OnPage and Connectwise integration works.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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

10 Experts available now in Live!

Get 1:1 Help Now