CODEWITHSUNDEEP
mysqlcountbetween
wjhulzebosch
wjhulzebosch

Reputation: 175

How to find duplicates between two variable dates

I have the following table:

id | whenCreated    | callingNumber |
---|--------------------------------|
1  | 1-1-2016 9:00  | 0612345678    |
2  | 1-1-2016 9:10  | 0623456789    |
3  | 1-1-2016 9:55  | 0612345678    |
4  | 1-1-2016 10:15 | 0623456789    |
5  | 1-1-2016 11:00 | 0623456789    |
etc.

Datatypes:
id = int (AI)
whenCreated = datetime
callingNumber = varchar

I want to do an analysis on how many people call back within a certain period after their previous call (for the example, let's say 1 hour). For this, i want to find the number of times someone has called before in the previous period. In this example, the result should be something like this:

id | whenCreated    | callingNumber | prevCalls |
---|--------------------------------|-----------|
1  | 1-1-2016 9:00  | 0612345678    | 0         | < 0, because no previous calls
2  | 1-1-2016 9:10  | 0623456789    | 0         | < 0, because no previous calls
3  | 1-1-2016 9:55  | 0612345678    | 1         | < 1, because one call from this number in the last hour
4  | 1-1-2016 10:15 | 0623456789    | 0         | < 0, there was one earlier call from this number, but it was more than an hour ago
5  | 1-1-2016 11:00 | 0623456789    | 1         | < 1, because one call from this number in the last hour
etc.

Is there any way i can do this with one query in MySQL? Or do i have to run a script for this? (i know how to do it for each row individually, i just can't seem to find a way to do it with one query).

Upvotes: 1

Views: 112

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can do it with a correlated subquery:

SELECT id, whenCreated, callingNumber,
       (SELECT COUNT(*)
        FROM mytable AS t2
        WHERE t2.callingNumber = t1.callingNumber AND
              t2.id < t1.id AND 
              TIMESTAMPDIFF(MINUTE, t2.whenCreated, t1.whenCreated) < 60)
FROM mytable AS t1

The query uses TIMESTAMPDIFF to calculate the difference in minutes between two whenCreated values.

Demo here

Upvotes: 1

Related Questions