# Miscellaneous > Ask an Expert >  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!

----------


## RobG

Hello nosepicker, I realize this is a very old post.  I wanted to know if you could help me write a SQL Server query of along the lines of your  table below

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 
I need to write to a query.  I would like to pull the elapsed time as noted above and write the query to another table for results.  When I use Insert to it errors out.

----------

