asked
How to calculate average value in sql server
I am trying to calculate the average time a proc is executed from trace file I have collected, I have not work as a developer so I google it and got these three ways they all gave me the same result but my question what is the outcome is it in Second, Minute .. Thank you for your help declare @T table(StartTime datetime) INSERT INTO @T SELECT [StartTime] FROM table_name ;with C(Sec) AS ( select dateadd(SECOND, avg(datediff(second, dateadd(day, datediff(day, 0, StartTime), 0), StartTime)), 0) from @T ) select convert(char(5), dateadd(minute, case when datepart(second, C.Sec) >= 30 then 1 else 0 end, C.Sec), 108) AS Avg_Value from C SELECT CONVERT(Varchar(50), CAST( AVG( CAST( [StartTime] - CAST([StartTime] AS DATE ) AS DECIMAL( 18, 6 ) ) ) AS DATETIME ),114) From Table_name SELECT DATEADD(SECOND, AVG( DATEDIFF( SECOND, 0, CAST([StartTime] as TIME) ) ), 0) FROM ...Table_name
The average start time? So if it executed one day at 4pm, and then the next day at 5pm, the average is 4:30pm?
thanks, this what I got from the first query declare @T table(StartTime datetime) INSERT INTO @T SELECT [StartTime] FROM table_name ;with C(Sec) as ( select dateadd(SECOND, avg(datediff(second, dateadd(day, datediff(day, 0, StartTime), 0), StartTime)), 0) from @T ) select convert(char(5), dateadd(minute, case when datepart(second, C.Sec) >= 30 then 1 else 0 end, C.Sec), 108) as Avg_Value from C Answer = 17:43 SELECT CONVERT(Varchar(50), CAST( AVG( CAST( [StartTime] - CAST([StartTime] AS DATE ) AS DECIMAL( 18, 6 ) ) ) AS DATETIME ),114) FROM Table_Name Answer = 17:43:19:660 SELECT DATEADD(SECOND, AVG( DATEDIFF( SECOND, 0, CAST([StartTime] as TIME) ) ), 0) FROM Table_Name answer = 1900-01-01 17:43:19.000 and number of records for the starttime = 5721 rows they all have 17:43 I am assuming that is the correct result but I could be wrong that is why I as is this in seconds or minutes thanks
Well I guess I am heading in the wrong direction, if you can help, I am trying to get how often a proc is executed, I have start time and endtime for each Proc Completed from trace file and have a record of about 200,000 rows (2 weeks trace) l, by the way it is only one proc, how can I tell this proc is executed like every 2 minutes, 5 minutes ... thanks
For quick analysis of trace files I sometimes use ClearTrace [
https://www.scalesql.com/cleartrace/] - it might help you. Otherwise if the trace is purely just for one proc, and you know the start and end time of the trace then you have a total duration. Divide that by the number of executions, and you'll have a rough idea of frequency.
thank you for help, here is what I came up with DECLARE @v1 DATETIME = '2017-10-11 10:20:00.260' -- beginning of trace DECLARE @v2 DATETIME = '2017-10-23 11:50:26.083' -- end of trace SELECT DATEDIFF(SECOND, @v1, @v2 ) AS Time_interval -- gave me the time interval in seconds between @v1 and @v2 =1042226 SELECT COUNT(*) FROM Table_name -- number of records traced = 201733 SELECT 1042226/201733 = 5.16 is it safe to say the proc was executing every 5.16 seconds thanks
I've converted my comment to an answer so you can vote it up (with the thumbs up icon), and/or mark it as an accepted answer (the tick mark)
17 People are following this question.
