Tuesday, May 10, 2011

Averaging Timespans in T-SQL

I'm doing some work with an operations queue. All of the operations are added to a small table on our SQL Server instance. We have a start_datetime and a stop_datetime for each operation. It took me a little time but here's a select statement for profiling the wait times for operations in our queue.

  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
  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.

No comments: