Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

SQL add time - convert

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
Vidalle
Asked:
Vidalle
1 Solution
 
momi_sabagCommented:
would the third column always be less than 1 hour?
what is the data type of the third column?
0
 
VidalleAuthor Commented:
Thanks, it will go over 1 hour.
0
 
Pratima PharandeCommented:
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
 
esbasitCommented:
for example : 65 minutes  result would like 01:05:00

select LEFT(Convert(Time, dateadd(minute,65,0)),8)
0
 
VidalleAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now