Monday, August 02, 2010

calculate performance by datediff() and aggregation() functions

As we store Raw data in our Database, which just history record without any calculation, just log start timestamp and end timestamp of each action/method. So here is a sample on how to calculate performance by datediff() and aggregation() functions:

declare @starttime varchar(50)

declare @endtime varchar(50)

set @starttime='03/08/2010 10:25:00.000'
set @endtime='03/08/2010 12:00:00.000'

select count(*), avg(datediff(millisecond,starttime, endtime)), min(datediff(millisecond,starttime, endtime)), max(datediff(millisecond,starttime, endtime))
from statistics_log
where event_type='send' and starttime > @starttime and starttime < @endtime

No comments:

Post a Comment