question

tmisganaw avatar image
tmisganaw 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
sql-server-2008sql server
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The average start time? So if it executed one day at 4pm, and then the next day at 5pm, the average is 4:30pm?
0 Likes 0 ·
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
0 Likes 0 ·
Based on that, 17:43 is the average start time i.e. 5:43pm
0 Likes 0 ·
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
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
Great. Does that fit in with your expectations?
0 Likes 0 ·
Yes it does fit
0 Likes 0 ·
Is there some way that I can put your help was Helpful
0 Likes 0 ·
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)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.