Solved

SQL add time - convert

Posted on 2013-01-08
5
278 Views
Last Modified: 2013-01-09
I need to convert minutes to hours and then group by attendee. I'm learning SQL but this one is stumping me. Any help would be greatly appreciated. I know, I need to remove the first column, I'm just frustrated and thought I would copy the code I had before I loose it. : )  Thanks again for the help.

Here is the code

SELECT     SUM(Time) AS TotalMinutes, AttendeeName, TotalNew
FROM         (SELECT     AttendeeName, Time, CONVERT(VARCHAR, Time / 60) + ':' + RIGHT('00' + CONVERT(VARCHAR, Time % 60), 2) AS TotalNew
                       FROM          OMUCourse AS OMUCourse_1) AS OMUCourse
GROUP BY AttendeeName, TotalNew

Current Results:

10      Dan Mahoney      0:10
15      Bryan Smith      0:15
15      Dan Mahoney      0:15
20      Bryan Smith      0:20
20      Bill Wright      0:20
20      Dan Mahoney      0:20
20      Ms Wendy Jordan      0:20
25      Dan Mahoney      0:25
25      Ms Wendy Jordan      0:25

Need These Results:

Dan Mahoney      1:05
Bryan Smith      0:35
Bill Wright      0:20
Ms Wendy Jordan      0:45
0
Comment
Question by:Vidalle
[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
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38757694
would the third column always be less than 1 hour?
what is the data type of the third column?
0
 

Author Comment

by:Vidalle
ID: 38757722
Thanks, it will go over 1 hour.
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 38757811
does Time column contain data in int which are minutes ?

then you can try this

Select AttendeeName ,
 CAST(Sum(Time)/ 60 AS VARCHAR(10)) + '.'
    + RIGHT('0' + CAST(Sum(Time) % 60 AS VARCHAR(2)), 2)
 as TOtal_new
from OMUCourse
Group by AttendeeName
0
 

Expert Comment

by:esbasit
ID: 38757901
for example : 65 minutes  result would like 01:05:00

select LEFT(Convert(Time, dateadd(minute,65,0)),8)
0
 

Author Closing Comment

by:Vidalle
ID: 38758463
This is exactly what I needed. Thanks for writing it out using my code. Since  I'm still learning, it made all the difference. Thanks again!
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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 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