Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Sql Date Help

  1. #1
    Join Date
    Apr 2006
    Posts
    12

    Question Unanswered: Sql Date Help

    I currently have a table showing starting and stopping times. And I would like the average time the action is going (ie. average of stop- start).

    Here is a sample table:

    Action Time
    Start 4/17/2006 1:23:45 PM
    Stop 4/17/2006 1:24:03 PM
    Start 4/17/2006 1:25:27 PM
    Stop 4/17/2006 1:26:15 PM

    So, there isn't really any other information besides this. I am having trouble doing the inner join with itself and figuring out how to only show one result, and to make it show the correct result.
    Any help is appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Table is next to useless.
    Records in a table contain NO implicit order.
    You have no field in the table to allow a JOIN of any start time with the correct stop time.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2006
    Posts
    12
    The assumption to make is that the order is based on time. Ie. The stop is for the preceeding start in time. Also, there is another row in the column called id which are unique, but are not in any order. I was thinking to have: select Avg(b.time - a.time) from mytable a, mytable b where b.Action = Stop and a.Action = Start. But, I need to get it to match them correctly, otherwise I would be returned with an average of four results: (stop1 - start1), (stop2 - start1), (stop1-start2), (stop2-start2). I also don't know the syntax for doing averages of time.

    Thanks
    Last edited by jman5436; 04-17-06 at 15:47.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Action Time
    Start 4/17/2006 1:23:45 PM
    Start 4/17/2006 1:25:27 PM
    Stop 4/17/2006 1:29:15 PM
    Stop 4/17/2006 1:34:03 PM

    Which stop time goes with which start time?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2006
    Posts
    12
    It goes in order, Ie. for every start, you look at all the stops, and get the earliest one, that is the stop that matches it.

  6. #6
    Join Date
    Apr 2006
    Posts
    12
    Clarification: For every start, you look at all the stops with time greater then it, and match it with the earliest one out of those.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'll stipulate that your "algorithm" will always produces answers.
    I contend that in a real world, multiuser application the answers will not always reflect reality.
    GIGO!
    Not all "transactions" run sequentially or have the same duration.
    Long duration START
    Short duration start
    Short duration stop
    Long duration stop.
    Your solution produces incorrect results in the case above.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Aug 2004
    Posts
    330
    What is enforcing the alternation of START/STOP actions and corresponding order of times? If the nature of this data is not enforced by the database, then you will open yourself up to undependable query results.

  9. #9
    Join Date
    Apr 2006
    Posts
    12
    You are to assume that you can not have a start action without the previous action being a stop.

    Therefore the only possible sequence will be:
    Start time a
    Stop time b
    Start time c
    Stop time d

    where a < b < c < d

    The actual database and information was simplified for the purpose of this forum.

    It seems that no one is helping to find a possible solution, except give criticism to the question.

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    havent tried it out so it may not work but it might (possibly need another subquery to select rownum but its too late for my brain to think straight )
    Code:
    select avg(stoptime-starttime) from
    (
         select max(timeval) stoptime, min(timeval)  starttime
         from 
         (
               select  timeval from table_of_times order by timeval asc
         )
         group by trunc(rownum/2)
    )
    Alan
    Last edited by AlanP; 04-17-06 at 18:28.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you can be sure that the sequence of rows is start/stop/start/stop/... then you can use this analytic query:
    Code:
    SQL> select avg(stop_time-start_time) avg_time
      2  from
      3  ( select action, time start_time, lead(time) over(order by time) as stop_time
      4    from t1
      5  )
      6  where action = 'Start';
    
      AVG_TIME
    ----------
    .000381944

  12. #12
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Alan, very clever.

  13. #13
    Join Date
    Apr 2006
    Posts
    12

    Thanks

    Right now I am testing with MS SQL, and it says the average function can not take a datetime data type as an argument, is there a way around this? Like, convert it to minutes?

    This is the solution that I have now, I just need to take the average of it.

    I tried what you guys gave me and it didn't quite work for me:
    Code:
    SELECT c.stop - c.start 
    FROM (Select MIN(a.time) as stop, b.start 
            from mytable a, 
                  (Select time as start from mytable where Action = 'Start') b
            where a.time>b.start and a.Action = 'Stop' GROUP BY b.start) c

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jman5436
    Right now I am testing with MS SQL, and it says the average function can not take a datetime data type as an argument, is there a way around this? Like, convert it to minutes?
    This is the Oracle forum, did you post in the wrong place? I for one have no idea what arguments MS SQL functions can and cannot take!

  15. #15
    Join Date
    Apr 2006
    Posts
    12
    Right now I am testing my solution on an MS SQL db, but the sql is supposed to work for oracle. I don't have that to test with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •