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

Thread: Time difference between rows in same column

  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Time difference between rows in same column

    Is there any ways to count the time difference between each row using sql? In the example, I would like to get the difference between row 1 and 2, row 2 and 3, etc. I am thinking I am pretty much stuck doing this in excel. All the dates and times in this table are in the same column.

    Any ideas would really be appreciated


    time/date
    1/3/05 8:00 AM
    1/3/05 8:55 AM
    1/3/05 9:00 AM
    1/3/05 9:13 AM

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    If it's on sql server, you can try datediff function.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Here you go.

    use tempdb
    go
    --drop table x
    go
    create table x (id int identity(1,1), date datetime,name varchar(100))
    go
    insert into x select '1/3/05 8:00 AM','A'
    insert into x select '1/3/05 8:55 AM','Adff'
    insert into x select '1/3/05 9:00 AM','ertr'
    insert into x select '1/3/05 9:13 AM','erer'
    go

    select * from x
    go
    select a.id,a.date, datediff(mi,(select b.date from x b where a.id=b.id+1),a.date )
    as timedifference from x a

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    result

    id,date,timedifference
    1,2005-01-03 08:00:00.000,
    2,2005-01-03 08:55:00.000,55
    3,2005-01-03 09:00:00.000,5
    4,2005-01-03 09:13:00.000,13

  5. #5
    Join Date
    Dec 2004
    Posts
    502
    MAK's method is good, but it might be a bit slow for a large table. Probably faster to do this (using his table):

    SELECT x1.id, x1.date, DATEDIFF(mi, x2.date, x1.date)
    FROM x AS x1 LEFT JOIN x AS x2
    ON x1.id = x2.id +1

  6. #6
    Join Date
    Aug 2005
    Posts
    31
    This appears to work, thanks all!

  7. #7
    Join Date
    Aug 2005
    Posts
    31
    Just an update on this, I have another simular scenario, but the data does not have indexes. Is there still a way to do this?
    Or is there a easy way of creating an index? Thanks again!

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Do you mean data are not ordered or there is no id column?

  9. #9
    Join Date
    Aug 2005
    Posts
    31
    there is no id column. I could put the data in order when I create the table if I need to. with the rows shown below, I would like to not include anything counted by the same agent within a 2 hour period. in this case I would not want to get the 2nd row out of the first 3, but I would like the first and the 3rd and 4th if that makes sense. and anything that is not a duplicate ordernumI would like to be able to pull. thanks again.


    ordernum datetime agent
    123456 01/03/06 7:00 33333
    123456 01/03/06 7:25 33333
    123456 01/04/06 10:00 33333
    123456 03/03/06 17:00 55555
    987654 04/30/06 12:00 77777
    Last edited by Iggy_Pop; 08-16-2006 at 04:32 PM.

  10. #10
    Join Date
    Dec 2004
    Posts
    502
    So what would happen in this scenario:

    ordernum datetime agent
    123456 01/03/06 7:00 33333
    123456 01/03/06 7:30 33333
    123456 01/03/06 9:15 33333
    123456 01/03/06 11:00 33333

    How do you know which ones to keep? You could delete the 7:30 and 9:15 records to have a 2 hour difference, but you could also delete the 7:30 and 11:00 records to accomplish the same. Or you could delete the 7:00 and 9:15 records too.

  11. #11
    Join Date
    Aug 2005
    Posts
    31
    Hopefully, in the scenario you describe below, I would want to base it off the first timestamp per agent #. So in that case, I would only get rid of the 7:30 timestamp since it is less than 2 hours from the first timestamp.

    This is what I have tried, but I am not getting the result set I am trying for

    select a.ordernum. a.agent, a.datetime
    from tbldata a
    where
    a.ordernum in (select b.ordernum FROM tbldata b where a.ordernum = b.ordernum AND a.agent = b.agent AND datediff(mi, a.datetime b.datetime) > 120)
    order by ordernum, agent, datetime

    Thanks for your help!

  12. #12
    Join Date
    Dec 2004
    Posts
    502
    Will this work?

    SELECT B.*
    FROM

    (SELECT ordernum, MIN([datetime]) AS mindatetime
    FROM YourTable
    GROUP BY ordernum) AS A

    JOIN YourTable AS B

    ON A.ordernum = B.ordernum
    AND (A.mindatetime = B.[datetime] OR DATEADD(minute, 120, A.mindatetime) <= B.[datetime])

  13. #13
    Join Date
    Aug 2005
    Posts
    31
    Very close!
    This is what the raw data looks like:
    agent ordernum datetime
    33333 123456 2/6/06 12:12
    33333 123456 2/6/06 12:13
    77777 123456 2/7/06 10:55
    77777 123456 2/7/06 11:17

    This is what it looks like after running your query:
    agent ordernum datetime
    33333 123456 2/6/06 12:12
    77777 123456 2/7/06 10:55
    77777 123456 2/7/06 11:17

    Is there a way to do this per agent #, per ordernum within 2 hours? Since the last record in the result set is within 2 hours of the second agent updating the order, It would be great if I could eliminate the record thats within 2 hours of 77777 first touching it as well. I wish I could just distinct out each agent/ordernum, but there are occurences where the agent handles the order several times outside of 2 hours. So its 1 update within 2 hours for each unique agent #. They can overlap eachother within the same time frame. Thanks again!

  14. #14
    Join Date
    Dec 2004
    Posts
    502
    Then try this:

    SELECT B.*
    FROM

    (SELECT ordernum, agent, MIN([datetime]) AS mindatetime
    FROM YourTable
    GROUP BY ordernum, agent) AS A

    JOIN YourTable AS B

    ON A.ordernum = B.ordernum
    AND A.agent = B.agent
    AND (A.mindatetime = B.[datetime] OR DATEADD(minute, 120, A.mindatetime) <= B.[datetime])

  15. #15
    Join Date
    Aug 2005
    Posts
    31
    appears to work! Thanks again!

Posting Permissions

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