?
Solved

Sql Server calculating total minutes and the hours with group by clause

Posted on 2009-04-28
1
Medium Priority
?
810 Views
Last Modified: 2012-05-06
I have a select query and want to calculate the total minutes and the hours. If total minutes greater than 59 I want to add 1 hour into the hours column and using the mod function (%) calculate the rest of minutes. How can it be done in  a best way? Thanks.
Query:

select LastName,SUM(hours) as TotalHours, SUM(minutes) as TotalMinutes
from Table1 AS PA
inner join table2 as OT
ON PA.ID=OT.ID
Group by LastName
---------------------------------
condition idea:

select CASE When sum(minutes)>59 Then sum(minutes)%60 Else sum(minutes)
End as TotalMinutes From Table1
0
Comment
Question by:fycert55
1 Comment
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 2000 total points
ID: 24258555
Hi fycert55,
try this:
select LastName,SUM(hours) + (CASE WHEN SUM(minutes) > 59 THEN SUM(minutes) 
      / 60 ELSE 0 END) AS TotalHours, CASE WHEN SUM(minutes) 
      > 59 THEN SUM(minutes) % 60 ELSE SUM(minutes) END AS TotalMinutes
from Table1 AS PA
inner join table2 as OT
ON PA.ID=OT.ID
Group by LastName

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

850 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