Totaling Hours and Minutes

Question

Post reply

Totaling Hours and Minutes

ramses2nd

Hall of Fame

Points: 3720
More actions
January 24, 2012 at 10:25 am

#263243

I have the following data in a query:
CREATE TABLE EmpHours
(
EMP_ID INT NULL,
EMP_DATE_WORKED VARCHAR(50) NULL,
EMP_MINUTES_WORKED INT NULL,
EMP_TOTAL_TIME VARCHAR(50) NULL
)
GO
INSERT INTO EmpHours
VALUES(285,'Jan 09, 2012', 536, '08:56:00')
INSERT INTO EmpHours
VALUES(285, 'Jan 10, 2012', 471, '07:51:00')
INSERT INTO EmpHours
VALUES(285, 'Jan 11, 2012', 483, '08:03:00')
INSERT INTO EmpHours
VALUES(285, 'Jan 12, 2012', 489, '08:09:00')
INSERT INTO EmpHours
VALUES(285, 'Jan 13, 2012', 546, '09:06:00')
What I need is a query that will add and group these times. So if I were to group by the EMP_ID I would need to see the total hours worked would be 42:05:00. I seem to hung up and figuring out how extactley the best way to do this is. Any help or advice would be greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

SELECT

EMP_ID,

ROUND(SUM(CAST(EMP_MINUTES_WORKED as money))/60 ,2) [EmpHours]

FROM EmpHours

GROUP BY EMP_ID

?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Paul White SSC Guru Points: 150467 More actions · Answer 2

SELECT

RIGHT(100 + (Grouped.TotalMinutes / 60), 2)

+ ':' +

RIGHT(100 + (Grouped.TotalMinutes % 60), 2)

+ ':00'

FROM

(

SELECT

TotalMinutes = SUM(eh.EMP_MINUTES_WORKED)

FROM dbo.EmpHours AS eh

GROUP BY

eh.EMP_ID

) AS Grouped;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jared SSC Guru Points: 61793 More actions · Answer 3

Just curious... In your sample data are you giving us the entire actual table, or not? My guess is that there is a clock in datetime and a clock out datetime and that it is not separated as you are showing us?

Jared
CE - Microsoft

Paul White SSC Guru Points: 150467 More actions · Answer 4

SQLKnowItAll (1/24/2012)
Just curious... In your sample data are you giving us the entire actual table, or not? My guess is that there is a clock in datetime and a clock out datetime and that it is not separated as you are showing us?

Good question. I would also hope that the real table has at least the DateWorked column properly typed as 'date' instead of varchar(50). My assumption was that the question is really about how to sum intervals and display them in '[dd]:'hh:mm:ss' format.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ramses2nd Hall of Fame Points: 3720 More actions · Answer 5

Yes the source table for this data does include a clock in and clock out time but I could not use thoses fields in the calculation since there are several in/out events by several people throughout the week long period. So based on that I was able to break it down to a total minutes for each day but am just having trouble adding all the minutes up and showing them in a nice format when they go over that 24 hour period.

Jared SSC Guru Points: 61793 More actions · Answer 6

ramses2nd (1/24/2012)
Yes the source table for this data does include a clock in and clock out time but I could not use thoses fields in the calculation since there are several in/out events by several people throughout the week long period. So based on that I was able to break it down to a total minutes for each day but am just having trouble adding all the minutes up and showing them in a nice format when they go over that 24 hour period.

Well, in theory we can help you with that or already have. However, it may be best to start from the source data and maybe we can help you come to a better solution? Just a suggestion 🙂

Jared
CE - Microsoft

ramses2nd Hall of Fame Points: 3720 More actions · Answer 7

Thanks I was able to combine a couple of the suggestions and come up with a good solution.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

ramses2nd (1/24/2012)
Thanks I was able to combine a couple of the suggestions and come up with a good solution.

Two way street here. Please share your solution. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)