select
CONVERT(VARCHAR(13),creation_date,120) as [hour],
CONVERT(VARCHAR(8), max(stop_datetime - start_datetime), 108) as MaxWaitTime,
CONVERT(VARCHAR(8), min(stop_datetime - start_datetime), 108) as MinWaitTime,
CONVERT(VARCHAR(8), cast(avg(cast(stop_datetime - start_datetime as float)) as datetime), 108) as AvgWaitTime
from
[OpQueue].[Op]
where
start_datetime > '2011-05-07'
group by
CONVERT(VARCHAR(13),start_datetime ,120)
Here are a few things to note.
This statement will spit out the min, max, and average running time for operations in the queue that start in the same hour.
The third argument for convert is pretty handy for DATETIMEs. Here is the page where it is described: http://msdn.microsoft.com/en-us/library/ms187928.aspx.
The AVG() function doesn't work for DATETIMEs, so we need to convert it to a float and then back again to get what we are looking for.
Here's the graph we ended up with.
Seems like it's time to invest in some more processing power.