Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Using date from prior record

    Hello gurus, I have a table of data containing stop and start times of equipment, such as this:

    StartTime....................EndTime
    12/01/01 15:44...........12/01/01 18:44
    12/02/01 3:44............12/02/01 14:44
    12/02/01 15:22...........12/02/01 15:33

    etc.

    With this, I can build a query that subtracts the start and end dates to give me the total differences between each record... But, how do I write a query that can count the elapsed time between the current record's "start time" and the prior record's "end time" to give me the elapsed time between each record?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If the table also contains an integer field, preferably an IDENTITY, then it's a piece of cake:

    select datediff(second, b.EndTime, a.StartTime)
    from dbo.[table] a inner join dbo.[table] b
    on b.IDENTITY_FIELD > a.IDENTITY_FIELD

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest something resembling:
    PHP Code:
    SELECT a.StartTime, (SELECT Max(b.EndTime)
       
    FROM dbo.myTable AS b
       WHERE  b
    .EndTime a.StartTime)
       
    FROM dbo.myTable AS 
    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Something like this:

    select YourTable.PrimaryKey,
    datediff(mi, Max(PriorRecords.EndTime), CurrentRecords.StartTime) as ElapsedMinutes
    from YourTable CurrentRecords
    left outer join YourTable PriorRecords on CurrentRecords.StartTime > PriorRecords.EndTime
    group by YourTable.PrimaryKey, CurrentRecords.StartTime
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and by the way, rdjabarov, your logic fails if the order of the identity field does not match the order of the time values, and your inner join will fail to return the initial record.

    You could have found this in the old Books Online, but then you probably don't "give a rat's ass" anyway....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Lindman does vengence

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...And the logic will not fail, Lindman, because of the nature of data collection, don't you get it? It's the "equipment up/down time" for Pete's sake! Would you expect it to post the entry for today before it does it for yesterday? If THAT is the case, then I'd assume you had something to do with writing it

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are making assumptions about the data without any basis for them. It is never a good idea to rely on identity values for ordering data. Updates, bulk loads, inputs from remote environments; all can cause the data to be ordered differently than the event time.

    If I wrote it, these issues would be anticipated and the design would be robust enough to handle it. That's what makes me a developer and you an admin, so stick to your backups and log dumps.

    ...and along those lines, Cheeto, you should consider using:

    CurrentRecords.StartTime >= PriorRecords.EndTime

    instead of:

    CurrentRecords.StartTime >= PriorRecords.EndTime

    if there is a chance that one date range could begin at the same time the previous one finishes.
    Last edited by blindman; 04-08-04 at 22:02.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    wonder if cnn has been informed about this conflict. looks like it's international too.

    isn't it a little harsh? how much do you know of each other to through around words like these? are related?

    oh well, who cares, looks like a dead thread except for those 2.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by ms_sql_dba
    wonder if cnn has been informed about this conflict. looks like it's international too.

    isn't it a little harsh? how much do you know of each other to through around words like these? are related?

    oh well, who cares, looks like a dead thread except for those 2.
    International? I've never met either of them in person, but even I'm not that big (yet)!

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't sell yourself short, Pat! While I merely have people pestering me on forums, you have whole web pages devoted to vitriolic attacks against you!

    http://www.dbdebunk.com/page/page/622139.htm

    What is with these guys, anyway? I read page after page about what they hate about RDBMS and popular database systems, but I've yet to come across any concrete information about what they would propose instead. Long on theory, short on implementation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    i'd stay away from those 2 for awhile, one of them already tried to pick a fight with me...i guess the other one is not here so why not, right?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by ms_sql_dba
    i'd stay away from those 2 for awhile, one of them already tried to pick a fight with me...i guess the other one is not here so why not, right?
    Which two? Blindman and rdjabarov or Fabian Pascal and Chris Date? I've argued with all of them, won some, lost some with each of them. I like to think that I've treated them all professionally, although Fabian seems to have an almost vitriolic dislike for me and everything I believe in!

    It takes a lot to get me worked up. I'm generally pretty good at putting out the fires that fuel long term fights because when push comes to shove I'm pretty good at sticking to the facts at hand, and presenting them in logical fashion. It is really hard to argue over things you can measure, especially when at least the gross outcome is consistent (even if the measurements vary slightly).

    I save my religious fervor for religion. I don't get too wound up over languages, specific algorithms, etc. I try to keep an open mind, especially when I don't like the person offering the opinion. I have a bad habit of speaking my mind, and calling a spade a spade, but most of the time folks come to appreciate that rather than hating me for it.

    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and you call yourself a "Curmudgeon".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by blindman
    Don't sell yourself short, Pat! While I merely have people pestering me on forums, you have whole web pages devoted to vitriolic attacks against you!

    http://www.dbdebunk.com/page/page/622139.htm

    What is with these guys, anyway? I read page after page about what they hate about RDBMS and popular database systems, but I've yet to come across any concrete information about what they would propose instead. Long on theory, short on implementation.
    Oh heavens, Fabian has done much worse than that.

    You would have died laughing at a comment that r937 made regarding the fine folks at http://www.dbdebunk.com/ and my relationship with them. Unfortunately, someone here at dbforums seems to have "pulled the plug" without warning on the original version of the Yak Corral, which I think had some priceless content. I really, really wish that I'd saved a copy of it!

    -PatP

Posting Permissions

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