I am trying to create a report that will display by technician the average, maximum and minimum time required to resolve a request (Not looking for time spent) within a certain date range. For example I want all of the technician averages for last month or last year. I am guessing the most accurate time for this is the difference between the workorder.createdtime and workorderhistory.operationtime for Operation=close. I was able to write the following which gets me close to what I am looking for but does not account for operational hours in the calculation and I am having difficulty with the date portion of the where clause.

MYSQL Query

select

            aa.first_name,

            avg((woh.operationtime-wo.createdtime)/1000) as Average_Resolution_Time,

            max((woh.operationtime-wo.createdtime)/1000) as Max_Resolution_Time,

            min((woh.operationtime-wo.createdtime)/1000) as Min_Resolution_Time

from

            workorder wo left join workorderhistory woh on wo.workorderid=woh.workorderid

            left join aaauser aa on woh.operationownerid=aa.user_id

where woh.operation='CLOSE'

group by aa.first_name

Result Set from above query

First_name       Average_Resolution_Time        Max_Resolution_Time  Min_Resolution_Time

Cheryl              478085.43966929                   5951048.318                           1108.136

Chris                102626.15656034                   853813.274                             71.612

Gary                  114991.466                             264103.575                             10343.718

Mike                199602.90156937                   13488890.929                         11.562

Now ideally I would like to have the end result only count accumulated time between the created date and the date the work order was closed for operational hours. Also I would like to get the resulting time to express in  Hours:Minutes:Seconds which I am having some issues with as well. Any help would be appreciated.

What I am shooting for:

Result Set: Technician Average, Maximum and Minimum time to resolve issue for a given date period and configured operational hours

For September 2009     Operational Hours: M-F 8-5:30pm

First_name       Average_Resolution_Time        Max_Resolution_Time  Min_Resolution_Time

Cheryl              6 Days 4:32:55                         14 Days 2:10:03                  2:16:05

Chris                1 Day  1:05:24                           5 Days 5:01:52                     18:02

Gary                             3:18:47                            1 Day 1:41:42                       4:42

Any help would be appreciated. Breaking down days nice but not necessary. Just HH:MM:SS would work. Thanks again.