Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Finding first and last record in a set and calculating the time

    Howdy,

    I know I can do this manually but I'm wondering if there's a way to code this as all one query.

    I have a bunch of data in sets where each set has one column the same. Each row also has s time stamp on it.

    I want to find the first instance of a set and the last instance of a set and then calculate the time difference between them to see how long that set was active.

    SO my data might look like this:

    Code:
    ID   Time      text
    1    5:30:00   start
    1    5:31:00   middle
    1    5:33:00   end
    2    5:31:00   start
    2    5:35:00   middle
    2    5:39:00   end
    And the info I want to get back is:
    1 - 2:00
    2 - 8:00

    Is there an easy way to do that?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT id, max(time_column) - min(time_column)
    FROM your_table
    GROUP BY id;

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    Oh really? I didn't know you could do math in the select statement. I guess it's a lot easier than I thought it would be.

    Thanks.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is the time_column a string or a date variable?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    Is the time_column a string or a date variable?
    I assumes so because of:
    Quote Originally Posted by Kelemvor33
    Each row also has a time stamp on it

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >And the info I want to get back is:
    >1 - 2:00
    Why is "2:00" the desired/correct value?
    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.

Posting Permissions

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