Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to derive avg execuion time for 3 tables Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 11:23 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:35 AM
Points: 662, Visits: 957

Need to derive avg execution time per row in table for each tablename. Sample table layout

TableName BeginTime EndTime
========= ========= =======
T1 2013-06-02 00:30.10.073 2013-06-02 00:30.17.463
T1 2013-05-14 00:40.09.283 2013-05-14 00:42.10.640
T1 2013-06-22 00:52.02.073 2013-06-22 01:03.10.727
T1 2013-07-01 00:03.07.487 2013-07-01 00:04.03.120

T2 2013-03-06 00:16.10.763 2013-03-06 02:21.10.133
T2 2013-07-02 00:22.10.880 2013-07-02 01:12:04.282
T2 2013-05-12 00:01.10.113 2013-05-12 00:44.15.797

T3 2013-01-18 00:14.10.577 2013-01-18 02:02.19.120
T3 2013-07-02 00:23.10.560 2013-07-02 01:42.03.480

So I need avg time for T1, T2, and T3... just these 3 values.

thanks for any suggestions





BT
Post #1469681
Posted Tuesday, July 2, 2013 12:13 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:41 AM
Points: 617, Visits: 2,893
I think this will do the trick:

-- (1) Create table and sample data: 
DECLARE @x TABLE
( TableName varchar(3) not null,
BeginTime datetime not null,
EndTime datetime not null);

INSERT @x
SELECT 'T1', '2013-06-02 00:30:10.073','2013-06-02 00:30:17.463' UNION ALL
SELECT 'T1', '2013-05-14 00:40:09.283','2013-05-14 00:42:10.640' UNION ALL
SELECT 'T1', '2013-06-22 00:52:02.073','2013-06-22 01:03:10.727' UNION ALL
SELECT 'T1', '2013-07-01 00:03:07.487','2013-07-01 00:04:03.120' UNION ALL
SELECT 'T2', '2013-03-06 00:16:10.763','2013-03-06 02:21:10.133' UNION ALL
SELECT 'T2', '2013-07-02 00:22:10.880','2013-07-02 01:12:04.282' UNION ALL
SELECT 'T2', '2013-05-12 00:01:10.113','2013-05-12 00:44:15.797' UNION ALL
SELECT 'T3', '2013-01-18 00:14:10.577','2013-01-18 02:02:19.120' UNION ALL
SELECT 'T3', '2013-07-02 00:23:10.560','2013-07-02 01:42:03.480';

-- (2) Query
WITH exe_times AS
( SELECT TableName, DATEDIFF(SECOND,BeginTime,EndTime) AS exeTime
FROM @x)
SELECT TableName, AVG(exeTime) AS [Execution Time(seconds)]
FROM exe_times
GROUP BY TableName;

Note that I had to fix your date format in your sample data for the query to work; there was only one value (The second endtime for T2) that was in the correct format.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1469700
Posted Tuesday, July 2, 2013 12:17 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:35 AM
Points: 662, Visits: 957
AJB, thank you very much. This is great. (apologize for the bad date formats as I hand entered these from a PDF doc) Thx Again!!!

BT
Post #1469703
Posted Tuesday, July 2, 2013 12:31 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:41 AM
Points: 617, Visits: 2,893
No problem!

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1469710
Posted Tuesday, July 2, 2013 12:38 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:58 AM
Points: 546, Visits: 1,061
I think Alan and I had the same idea but he's just a little quicker on the keyboard. :)

 select *
from
(
select tablename, avg(datediff(ms,starttime,endtime)) over (partition by tablename) averageMS from timetest
) source
group by tablename, averageMS

Post #1469714
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse