Sub total of Hours and Minutes

Question

Sub total of Hours and Minutes

ramses2nd

Hall of Fame

Points: 3720
More actions
January 24, 2012 at 9:04 am

#251801

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 am stuck on is how to add a calculation in a total line within the report to sum all of the hours worked. So given the above data I would want a total column which reads something like 42:05:00. Any advice would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You'll have to create a formula. Multiply the hours by 60, sum them up, add in the sum for the minutes. That gives you minutes worked. Divide by 60, and you have hours, and the remainder (modulus) is minutes.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Duplicate post. Direct all replies here. http://www.sqlservercentral.com/Forums/Topic1241027-392-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

Thanks for all the suggestions I was able to figure this out with a combination of a couple of the examples.