January 24, 2012 at 10:25 am
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.
January 24, 2012 at 11:53 am
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
January 24, 2012 at 12:52 pm
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
January 24, 2012 at 12:55 pm
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
January 24, 2012 at 12:58 pm
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
January 24, 2012 at 1:02 pm
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.
January 24, 2012 at 1:06 pm
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
January 24, 2012 at 1:17 pm
Thanks I was able to combine a couple of the suggestions and come up with a good solution.
January 27, 2012 at 8:10 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply