CODEWITHSUNDEEP
sqlsql-serverdate
user3442107
user3442107

Reputation: 65

SQL Server- find all records within a certain date (not that straightforward!)

Ok. My SQL is pretty pants so I'm struggling to get my head around this.

I have a table that stores records complete with a time stamp. What I want, is a list of uids where there are 2 or more records for that user within a time frame of 1 second of each other. Maybe I've made it more complicated in my head, just cannot figure it out.

Shortened version of table (pk ignored)

    uid date
    1   2015-01-01 10:00:30.020*
    1   2015-01-01 10:00:30.300*
    1   2015-01-01 10:00:30.500*
    1   2015-01-01 10:00:39.000
    1   2015-01-01 10:00:35.000
    1   2015-01-01 10:00:37.800
    2   2015-02-02 12:00:30.000
    2   2015-02-02 14:00:30.000
    2   2015-02-02 15:00:30.000
    2   2015-02-02 18:00:30.000 
    3   2015-03-02 15:00:24.000 
    3   2015-03-02 15:00:20.000 *
    3   2015-03-02 15:00:20.300 *

I've marked * next to the records I'd expect to match. The results list I'd like is just a list of uid, so the result I'd want would just be 1 3

Upvotes: 1

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can do this with exists:

select distinct uid
from t
where exists (select 1
              from t t2
              where t2.uid = t.uid and
                    t2.date > t.date and
                    t2.date <= t.date + interval 1 second
             );

Note: The syntax for adding 1 second varies by database. But the above gives the idea for the logic.

In SQL Server, the syntax is:

select distinct uid
from t
where exists (select 1
              from t t2
              where t2.uid = t.uid and
                    t2.date > t.date and
                    t2.date <= dateadd(second, 1, t.date)
             );

EDIT:

Or, in SQL Server 2012+, a faster alternative is to use lead() or lag():

select distinct uid
from (select t.*, lead(date) over (partition by uid order by date) as next_date
      from t
     ) t
where next_date < dateadd(second, 1, date);

If you want the records, not just the uids, then you need to get both:

select t.*
from (select t.*,
             lag(date) over (partition by uid order by date) as prev_date,
             lead(date) over (partition by uid order by date) as next_date
      from t
     ) t
where next_date <= dateadd(second, 1, date) or
      prev_date >= dateadd(second, -1, date);

Upvotes: 1

Related Questions