Time difference between rows in same column

[Iggy_Pop]

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

[rmiao]

If it's on sql server, you can try datediff function.

[MAK]

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

[MAK]

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

[nosepicker]

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

[Iggy_Pop]

This appears to work, thanks all!

[Iggy_Pop]

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!

[rmiao]

Do you mean data are not ordered or there is no id column?

[Iggy_Pop]

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

[nosepicker]

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.

[Iggy_Pop]

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!

[nosepicker]

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])

[Iggy_Pop]

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!

[nosepicker]

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])

[Iggy_Pop]

appears to work! Thanks again!