none
Average Time Difference

    Question

  • I have a table that looks like this:


    id profileid action datetime
    1 524123 STARTED 01/19/2011 08:00:00
    2 524123 FINISHED 01/19/2011 08:30:00
    3 182901 STARTED 01/19/2011 09:00:00
    4 819102 STARTED 01/19/2011 09:05:00
    5 182901 FINISHED 01/19/2011 09:15:00
    6 819102 FINISHED 01/19/2011 09:35:00
    7 284102 STARTED 01/19/2011 10:05:00


    I want to find out what the average time is to go from Started to Finished. We have three people being finished, thirty minutes, fifteen minutes, and thirty minutes. So the average time is 25 minutes (although I'd like the results in seconds, 1500).

    I'm pretty new to SQL so if someone could help me out I would appreciate it. I'm sure I'm going to have to use DATEDIFF and AVG, I'm just not sure how to get the start and end dates in there.

    Thanks!
    Friday, January 21, 2011 3:55 PM

Answers

  • Try:

     

    select AVG(datediff(minute, S.Started, F.Finished)*1.0)  as AvgTime from Results S INNER JOIN Results F
    
    ON S.ProfileID = F.ProfileID and S.Action = 'STARTED' and F.Action = 'FINISHED'
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, January 21, 2011 4:13 PM
    Moderator
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

     

    >> I'm pretty new to SQL so if someone could help me out I would appreciate it. I'm sure I'm going to have to use DATEDIFF and AVG, I'm just not sure how to get the start and end dates in there. <<

     

    Let's fix the time format and make guesses at correct data element names. Then let's change the DDL since it is fundamentally wrong and never should be written this way. First we have to guess at a table name then drop that silly, vague magical, generic "id' column. Why did you use a reserved word for a column name? Etc.

     

    CREATE TABLE Profiles

    (profile_id CHAR(6) NOT NULL PRIMARY KEY

    CHECK (profile_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')

    profile_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,

    profile_end_time DATETIME, -- null means in progress

    CHECK ( profile_start_time < profile_end_time));

     

    You can use row constructors in T-SQL now:

     

    INSERT INTO Profiles

    VALUES

    ('524123', '2011-01-19 08:00:00', '2011-01-19 08:30:00'),

    ('182901', '2011-01-19 09:00:00', '2011-01-19 09:15:00'),

    ('819102', '2011-01-19 09:05:00', '2011-01-19 09:35:00'),

    ('284102', '2011-01-19 10:05:00', NULL);

     

    SELECT AVG (DATEDIFF(MINUTE, profile_start_time, profile_end_time) )

    AS profile_time_avg

    FROM Profiles

     

    What you did was directly copy a paper form from a clipboard, right down to the line numbers. That is not RDBMS. Time is a continuum, and has to be modeled with (start, end) pairs. Look up the ISO model for time and some of my articles.

     

    >> I want to find out what the average time .. <<

     

    SELECT AVG (DATEDIFF(MINUTE, profile_start_time, profile_end_time))

    AS profile_time_avg

    FROM Profiles

     

    If you wan to include the unfinished work, then use "COALESCE (profile_end_time, CURRENT_TIMESTAMP)" for the third argument.

     

     

     

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Friday, January 21, 2011 6:32 PM

All replies

  • Hi,

    http://msdn.microsoft.com/library/ms189794.aspx

    Do AVG (DATEDIFF (ss ,startdate ,enddate ))

    Regards,

    Stef

    Friday, January 21, 2011 4:07 PM
  • Try:

     

    select AVG(datediff(minute, S.Started, F.Finished)*1.0)  as AvgTime from Results S INNER JOIN Results F
    
    ON S.ProfileID = F.ProfileID and S.Action = 'STARTED' and F.Action = 'FINISHED'
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, January 21, 2011 4:13 PM
    Moderator
  • But how do I define startdate and enddate?  I have hundreds of rows of started and stopped (there's actually other actions too, but I'm just trying to start with these).
    Friday, January 21, 2011 4:14 PM
  • DECLARE @T TABLE([ID] INT, [ProfileID] INT, [Action] VARCHAR(10), [DateTime] DATETIME)
    INSERT @T
    SELECT 1, 524123, 'STARTED', '01/19/2011 08:00:00' UNION ALL
    SELECT 2, 524123, 'FINISHED', '01/19/2011 08:30:00' UNION ALL
    SELECT 3, 182901, 'STARTED', '01/19/2011 09:00:00' UNION ALL
    SELECT 4, 819102, 'STARTED', '01/19/2011 09:05:00' UNION ALL
    SELECT 5, 182901, 'FINISHED', '01/19/2011 09:15:00' UNION ALL
    SELECT 6, 819102, 'FINISHED', '01/19/2011 09:35:00' UNION ALL
    SELECT 7, 284102, 'STARTED', '01/19/2011 10:05:00'
    
    SELECT AVG(DATEDIFF(SECOND, [STARTED], [FINISHED]))
    FROM(SELECT [ProfileID], [Action], [DateTime] FROM @T) AS TBL
    PIVOT(MAX([DateTime]) FOR [Action] IN([STARTED], [FINISHED])) AS PVT
    
    SELECT AVG(DATEDIFF(SECOND, TBLA.[DateTime], TBLB.[DateTime]))
    FROM @T TBLA
    LEFT JOIN @T TBLB ON TBLB.[ProfileID] = TBLA.[ProfileID] AND TBLB.[Action] = 'FINISHED'
    WHERE TBLA.[Action] = 'STARTED'
    
    

    Either of the above should do the trick, assuming there are only ever two rows for each [ProfileID].

    Dave

    Friday, January 21, 2011 4:14 PM
  • HI MatTheZergling !

    I would suggest you to use the following query;

    WITH MyTable AS

    (

    SELECT ProfileID ,

    MAX(CASE [Action] WHEN 'STARTED' THEN [DateTime] ELSE NULL END) AS StartDateTime ,

    MAX(CASE [Action] WHEN 'FINISHED' THEN [DateTime] ELSE NULl END) AS EndDateTime

    FROM #Table

    GROUP BY ProfileID

    )

     

    SELECT AVG(DATEDIFF(n,StartDateTime,EndDAteTime))*60 AS AvergaeSeconds FROM MyTable

     

    Please let me know if this doesn’t work for you. Hope I have answered you correctly

    Thanks, Hasham

     

    Friday, January 21, 2011 4:57 PM
    Answerer
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

     

    >> I'm pretty new to SQL so if someone could help me out I would appreciate it. I'm sure I'm going to have to use DATEDIFF and AVG, I'm just not sure how to get the start and end dates in there. <<

     

    Let's fix the time format and make guesses at correct data element names. Then let's change the DDL since it is fundamentally wrong and never should be written this way. First we have to guess at a table name then drop that silly, vague magical, generic "id' column. Why did you use a reserved word for a column name? Etc.

     

    CREATE TABLE Profiles

    (profile_id CHAR(6) NOT NULL PRIMARY KEY

    CHECK (profile_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')

    profile_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,

    profile_end_time DATETIME, -- null means in progress

    CHECK ( profile_start_time < profile_end_time));

     

    You can use row constructors in T-SQL now:

     

    INSERT INTO Profiles

    VALUES

    ('524123', '2011-01-19 08:00:00', '2011-01-19 08:30:00'),

    ('182901', '2011-01-19 09:00:00', '2011-01-19 09:15:00'),

    ('819102', '2011-01-19 09:05:00', '2011-01-19 09:35:00'),

    ('284102', '2011-01-19 10:05:00', NULL);

     

    SELECT AVG (DATEDIFF(MINUTE, profile_start_time, profile_end_time) )

    AS profile_time_avg

    FROM Profiles

     

    What you did was directly copy a paper form from a clipboard, right down to the line numbers. That is not RDBMS. Time is a continuum, and has to be modeled with (start, end) pairs. Look up the ISO model for time and some of my articles.

     

    >> I want to find out what the average time .. <<

     

    SELECT AVG (DATEDIFF(MINUTE, profile_start_time, profile_end_time))

    AS profile_time_avg

    FROM Profiles

     

    If you wan to include the unfinished work, then use "COALESCE (profile_end_time, CURRENT_TIMESTAMP)" for the third argument.

     

     

     

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Friday, January 21, 2011 6:32 PM