Time difference between rows in same column

[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!