SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rounding on time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/19/2013 :  10:48:27  Show Profile  Reply with Quote
I am trying to convert minutes into hours as follows...

Minutes | Desired Result
13 0.0
60 1.0
90 1.5
98 1.5
200 3.25
230 3.75


Cheers!

Nev.


James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 06/19/2013 :  10:53:36  Show Profile  Reply with Quote
(Minutes/15*15)/60.0
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/19/2013 :  11:00:05  Show Profile  Reply with Quote
Nice one James. I must return to school to take maths again!!

Cheers.
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/19/2013 :  11:12:55  Show Profile  Reply with Quote
If I did want .25 in the event that the minutes were less than 15 would I have to use a CASE statement?

select CASE WHEN (DATEDIFF(MINUTE,on_site,Off_Site)/15*15) = 0
THEN 0.25 else
(DATEDIFF(MINUTE,on_site,Off_Site)/15*15) / 60.0
END







from call_events
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
79 Posts

Posted - 06/19/2013 :  12:05:34  Show Profile  Reply with Quote
Hi nevzab,
If Minute value is 45 then are u expecting 0.0 or 0.750000?

Thanks.

M.MURALI kRISHNA
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 06/19/2013 :  14:19:18  Show Profile  Reply with Quote
quote:
Originally posted by nevzab

If I did want .25 in the event that the minutes were less than 15 would I have to use a CASE statement?

select CASE WHEN (DATEDIFF(MINUTE,on_site,Off_Site)/15*15) = 0
THEN 0.25 else
(DATEDIFF(MINUTE,on_site,Off_Site)/15*15) / 60.0
END







from call_events

If you want to round up only for 0 to 15 minutes, then you would need a special case like you added.
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/19/2013 :  17:50:17  Show Profile  Reply with Quote
quote:
Originally posted by mmkrishna1919

Hi nevzab,
If Minute value is 45 then are u expecting 0.0 or 0.750000?

Thanks.

M.MURALI kRISHNA



I would expect 0.75. Anything <= 15 minutes = 0.25 otherwise round down to the nearest 15 minutes (0.25)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000