CODEWITHSUNDEEP
mysqlsql
Don Djoe
Don Djoe

Reputation: 705

MySQL find duplicate rows between certain days

I have a simple MySQL table called tickets

id  | operator_id | product_id |   created_timestamp
 1  |    STAFF001 |     acc001 | 2015-01-01 22:00:00
 2  |    STAFF003 |     acc004 | 2015-11-01 22:00:00
 3  |    STAFF002 |     acc002 | 2015-01-01 22:00:00
 4  |    STAFF002 |     acc003 | 2015-11-01 22:00:00
 5  |    STAFF001 |     acc005 | 2015-01-01 22:00:00
 6  |    STAFF005 |     acc002 | 2015-11-01 22:00:00
 7  |    STAFF004 |     acc001 | 2015-01-01 22:00:00
 8  |    STAFF001 |     acc001 | 2015-12-05 22:00:00
 9  |    STAFF003 |     acc001 | 2015-01-01 22:00:00
10  |    STAFF002 |     acc007 | 2015-11-01 22:00:00
11  |    STAFF001 |     acc001 | 2015-12-03 22:00:00
12  |    STAFF001 |     acc001 | 2015-12-01 22:00:00
13  |    STAFF005 |     acc001 | 2015-01-01 22:00:00
14  |    STAFF006 |     acc001 | 2015-12-01 22:00:00

How should I write the SQL so that I can retrieve the tickets that are created between 2 days of each other, which also have the same operator_id and product_id ?

So the expected result is (ignore the reason column, it is there just to help explaining)

id  | operator_id | product_id |   created_timestamp | reason
 8  |    STAFF001 |     acc001 | 2015-12-05 22:00:00 | because it is within 2 days from id 11
11  |    STAFF001 |     acc001 | 2015-12-03 22:00:00 | because it is within 2 days from id 8 or 12
12  |    STAFF001 |     acc001 | 2015-12-01 22:00:00 | because it is within 2 days from id 11

These are not included

id  | operator_id | product_id |   created_timestamp | reason
 1  |    STAFF001 |     acc001 | 2015-01-01 22:00:00 | the date diff is too big
14  |    STAFF006 |     acc001 | 2015-12-01 22:00:00 | the date diff is ok for the same product_id (e.g. compared to id 11 and 8 )but the operator_id is different
many other ...

Thanks for any help

Upvotes: 3

Views: 123

Answers (2)

cn0047
cn0047

Reputation: 17071

select *
from tickets t1
join tickets t2 on
    t1.id <> t2.id
    and t1.operator_id = t2.operator_id
    and t1.product_id = t2.product_id
    and ABS(DATEDIFF(t1.created_timestamp, t2.created_timestamp)) <= 2
;

I added ABS() function because, you have records where:
first ticket with date 2015-12-05 and id 8
second ticket with date 2015-12-03 and id 11

Upvotes: 2

mwpeng 彭明伟
mwpeng 彭明伟

Reputation: 96

select * from 
       tickets t1 left join tickets t2
       on t1.operator_id = t2.operator_id and t1.product_id = t2.product_id
       where t1.id != t2.id
        and datediff(r1.created, r2.created) <= 2

hope this could help.

Upvotes: 1

Related Questions