Post reply

Average Date time

  • October 14, 2003 at 7:47 am

    #82440

    I have a column with elapse time declared as varchar(20). But this contains the time difference between two datetime fields. (Is a 3rd party application). There is a category field in this table too. There are 5 different categories.How would I get

    average elapse time for each category. I am brain-dead trying to get this working.

    Thanks

    Raghu


    Raghu

  • October 14, 2003 at 8:23 am

    #478151

    Assuming the data is well-mannered --

    Could you just:

    SELECT Category, AVG( CONVERT( DATETIME, <ElapseTime> ) ) AS 'Ave Elapsed'

    FROM <Table>

    GROUP BY Category

    Guarddata-

  • October 14, 2003 at 8:59 am

    #478161

    I tried that...I get this errror

    Server: Msg 409, Level 16, State 2, Line 1

    The average aggregate operation cannot take a datetime data type as an argument.

    Raghu


    Raghu

  • October 14, 2003 at 9:35 am

    #478167

    hi,

    does the varchar(20) field contain a datetime ? or is the difference measured in seconds or minutes or days etc?

    Paul

  • October 14, 2003 at 9:45 am

    #478168

    Sample data for elapsetime is as :

    80:3:57

    80:3:54

    33:20:49

    58:8:20

    14:1:59

    7:22:17

    0:13:37

    5:6:0

    1:3:47

    0:22:57

    8:22:37

    4:4:44

    7:22:23

    27:18:12

    7:7:14

    3:0:20

    26:18:47

    0:2:9

    0:3:23

    1:14:54

    0:5:38

    0:5:29

    0:2:59

    0:5:36

    0:3:31

    1:1:38

    30:3:52

    25:17:59

    5:20:42

    4:1:23

    10:3:31

    1:22:47

    1:1:12

    5:20:18

    3:21:51

    3:17:56

    0:5:24

    17:23:28

    4:4:02

    3:17:52

    4:4:00

    3:2:07

    3:22:16

    3:22:16

    15:2:36

    1:5:08

    1:4:31

    5:20:34

    5:21:38

    5:21:30

    11:1:4

    7:7:31

    1:17:26

    1:1:42

    0:23:27

    0:2:0

    0:1:15

    0:1:53

    1:1:52

    0:20:41

    Thanks a lot

    Raghu


    Raghu

  • October 14, 2003 at 9:52 am

    #478171

    hi,

    is that hh:mm:ss ? or something different ?

    Paul

  • October 14, 2003 at 10:03 am

    #478175

    it is in hh:mm:ss format but the field carrying the data has varchar(20) datatype

    Raghu


    Raghu

  • October 14, 2003 at 10:19 am

    #478181

    hi,

    what you need to do is to use substring and charindex to get values for hours, minutes and seconds, and convert the value into a total of seconds, avg these and then present in the preferred format.

    I have to leave now (i'm in the UK), i'll pop back and have a look tomorrow if you haven't figured it out !

    Paul

  • October 14, 2003 at 11:19 am

    #478189

    I tried that and it works...even though it is somewhat ugly ! Also, I ignored Elapsetime field altogether and did a datediff on starttime and endtime (both datetime fields) and did an avg on datediff...that works !!! Now I am really confused...

    Raghu


    Raghu

  • October 15, 2003 at 1:46 am

    #478243

    datediff returns and integer , regardless of which datepart selected. This is why avg works. This is much neater than extracting it from a string !

    Paul

  • October 16, 2003 at 7:48 am

    #478456

    Thanks Paul

    Raghu


    Raghu

Viewing 11 posts - 1 through 10 (of 10 total)

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