Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Time Duration Addition Expand / Collapse
Author
Message
Posted Monday, February 16, 2009 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 12, 2012 9:44 AM
Points: 6, Visits: 51
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 Type
01/02/2009 10.29 1
01/03/2009 2.67 2
and 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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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 Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check 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 help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs 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

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse