Printable View
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
If it's on sql server, you can try datediff function.
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
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
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
This appears to work, thanks all!
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!
Do you mean data are not ordered or there is no id column?
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
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.
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!
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])
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!
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])
appears to work! Thanks again!
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.