with CTE_R as( select t.*, ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY LOGTIME) as RowNum from @MyTable as t)select c.UserID, c.LOGTIMEfrom CTE_R as cleft join CTE_R as a on a.UserID = c.UserID and a.RowNum = c.RowNum - 1where DATEDIFF(MINUTE, a.LOGTIME, c.LOGTIME) > 10 or c.RowNum = 1
UserID LogTimeU1 20111225 14:20:00U1 20111225 14:28:00U1 20111225 14:30:00U1 20111225 14:31:00U1 20111225 14:40:00U1 20111225 14:48:00U1 20111225 14:51:00U2 20111225 14:20:00U2 20111225 14:28:00U2 20111225 14:33:00U2 20111225 14:43:00U2 20111225 14:53:00U2 20111225 14:58:00U2 20111225 15:09:00