SQLTeam.com | Weblogs | Forums

How to search rows in a table


#1

I have the below rows:
User IP Date
ABC 1.2.3 6/1/2015 1:00 AM(mm/dd/yy)
ABC 1.2.4 6/2/2015 1:45 AM
ABC 1.2.4 6/5/2015 2:45 AM
ABC 1.2.3 6/5/2015 2:40 AM

I need to select the users which has multiple IP's and time period between them is less than 2 hrs. Based on my condition it should get me the last 2 rows.


#2
SELECT *
FROM Tbl t1
WHERE EXISTS
(
	SELECT * FROM Tbl t2
	WHERE t2.User = t1.User
	AND t1.ip <> t2.ip
	AND ABS(DATEDIFF(ss,t1.date,t2.date))/3600.0 < 2.0
)