Need to derive avg execuion time for 3 tables

  • [font="Courier New"]

    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

    [/font]

    BT
  • 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

    (SELECTTableName, 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.

    "I cant 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."

    -- Itzik Ben-Gan 2001

  • 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
  • No problem!

    "I cant 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."

    -- Itzik Ben-Gan 2001

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply