January 1, 2013 at 3:04 am
Hello
I want to select records such that if a user logs in again within 10 minutes it should ignore it, and only return the rows if the same user login after 10 or more minutes.
UserID LOGTIME
U1 25-12-2011 14:20:00
U1 25-12-2011 14:28:00 --ignore it
U1 25-12-2011 14:40:00
U1 25-12-2011 14:48:00 --ignore
note: datetime is in standard format, above values for example only
January 1, 2013 at 6:03 am
Hi,
Try:
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.LOGTIME
from CTE_R as c
left join CTE_R as a
on a.UserID = c.UserID and
a.RowNum = c.RowNum - 1
where DATEDIFF(MINUTE, a.LOGTIME, c.LOGTIME) > 10 or
c.RowNum = 1
Hope this helps.
January 1, 2013 at 6:08 am
qamar 52306 (1/1/2013)
HelloI want to select records such that if a user logs in again within 10 minutes it should ignore it, and only return the rows if the same user login after 10 or more minutes.
UserID LOGTIME
U1 25-12-2011 14:20:00
U1 25-12-2011 14:28:00 --ignore it
U1 25-12-2011 14:40:00
U1 25-12-2011 14:48:00 --ignore
note: datetime is in standard format, above values for example only
Really could use a little more in the sample data and expected results area. Given the following, what would you expect?
UserID LogTime
U1 20111225 14:20:00
U1 20111225 14:28:00
U1 20111225 14:30:00
U1 20111225 14:31:00
U1 20111225 14:40:00
U1 20111225 14:48:00
U1 20111225 14:51:00
U2 20111225 14:20:00
U2 20111225 14:28:00
U2 20111225 14:33:00
U2 20111225 14:43:00
U2 20111225 14:53:00
U2 20111225 14:58:00
U2 20111225 15:09:00
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply