Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Time Duration Addition Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 16, 2009 10:35 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, May 25, 2016 10:11 AM Points: 6, Visits: 59
 Hello,Was wondering - if someone has any suggestions - whereby which we can add time durations.Example: The Table looks like this:Date No. of Hours Worked Type01/02/2009 10.29 101/03/2009 2.67 2and so on ...Now - if we were to query the total no. of hours worked for Type 1 for the Month of January 2009 - any suggestions !!!Their is a way about going about it - by converting it into varchar, then splitting the : and taking the summation of the hours and the sumation of the minutes - which is to be converted again into hours and minute ...Any simpler suggestion pls !!! ...
Post #657930
 Posted Monday, February 16, 2009 12:46 PM
 SSChampion Group: General Forum Members Last Login: Today @ 7:36 AM Points: 10,808, Visits: 14,832
 Why can't you just use SUM? The data you show looks like it is hours and decimal part of the hour (2.67), not seconds so a SUM should work. Do you want to show hours and minutes instead of hours and decimal part of the hour (2.67)? Jack CorbettApplications Developer Don't let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance ProblemsCrosstabs and Pivots or How to turn rows into columns Part 1Crosstabs and Pivots or How to turn rows into columns Part 2
Post #658020
 Posted Monday, February 16, 2009 12:50 PM
 Right there with Babe Group: General Forum Members Last Login: Friday, February 6, 2015 9:34 AM Points: 759, Visits: 1,033
 You can try casting to datetime, then using datediff() to calculate the number of seconds.
Post #658023
 Posted Monday, February 16, 2009 1:34 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, October 24, 2012 2:12 PM Points: 1,212, Visits: 3,232
 Either format the total hours worked in front end, or if this is not applicable something like:`SELECT CONVERT(varchar(10), CONVERT(int, SUM(HoursWorked))) + ':' + RIGHT('0' + CONVERT(varchar(2), CONVERT(int, FLOOR(60.0 * SUM(HoursWorked) + 0.5)) % 60), 2)FROM ...`If the sum of the hours worked was guaranteed to be less than 24 hours then you could use the following, but I don't think this will work for you in this case.`SELECT CONVERT(varchar(5), DATEADD(minute, CONVERT(int, FLOOR(60.0 * SUM(HoursWorked) + 0.5)), 0), 108)`
Post #658059

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.