Break down a persons performance by hour

[mbryantuk]

Hi

I have a table containing the data a person has entered into the system e.g.
Agent|StartTime|EndTime
Bob|2009-01-22 09:59:59|2009-01-22 10:30:41
Bob|2009-01-22 10:44:59|2009-01-22 10:55:41
Bob|2009-01-22 11:02:59|2009-01-22 11:37:41
Bob|2009-01-22 11:42:59|2009-01-22 12:10:41

what i am looking to get out of the system is the following breakdown:
Agent|From|To|Duration
Bob|2009-01-22 09:00:00|2009-01-22 10:00:00|00:52:00 (This value should not be more than an hour)

Im not sure how i can do this, as grouping by datepart(hour,starttime) will mean if a rcord is entered near the end of the hour it will mean that hour will total over 1 hour,

Many thanks

 

[Snakeroot]

Have you looked at the FAQ's?

Take a look at this one in particular:

http://www.tek-tips.com/faqs.cfm?fid=6519

Using the function in the FAQ, you'll need to pass in the number of seconds between two datetime values. To get the number of seconds, you can use this:

Select (DATEDIFF(second,'2009-01-22 09:59:59','2009-01-22 10:30:41'))

So for the above example, the result is 00:30:42. You could add a column for the calculated times.

This should get you pointed in the right direction. Hope that helps.