CODEWITHSUNDEEP
t-sqlsql-server-2012
Shaz
Shaz

Reputation: 15877

Ignore results less than X time apart

The data I currently have looks something like the table I have below:

 UserId      VisitDate
 1           2012-01-01 00:15:00.000
 1           2012-01-01 00:16:00.000
 1           2012-01-12 00:15:00.000
 1           2012-01-12 00:16:00.000
 1           2012-01-24 00:15:00.000
 1           2012-01-24 00:16:00.000

I would like to return only results than have been 10 or more days apart so that it looks like so:

 UserId      VisitDate
 1           2012-01-01 00:15:00.000
 1           2012-01-12 00:15:00.000
 1           2012-01-24 00:15:00.000

Simpler than I'm making it perhaps, but how would one go about doing so in transact-sql?

Upvotes: 0

Views: 54

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11813

You can use the new lag() function in that case: http://msdn.microsoft.com/en-us/library/hh231256.aspx

Something like:

SELECT UserId, VisitDate
FROM(
  SELECT UserId,
         VisitDate, 
         LAG(VisitDate,1,'1900-01-01')OVER(PARTITION BY UserId ORDER BY VisitDate) PrevVisitDate
   FROM dbo.YourTable
)X
WHERE DATEDIFF(day,PrevVisitDate,VisitDate)>=10;

You can see it in action here: http://sqlfiddle.com/#!6/a0f02/1

Upvotes: 1

Related Questions