CODEWITHSUNDEEP
mysqlsql
Sourabh
Sourabh

Reputation: 1765

Getting new records in a certain time frame

My table design is somewhat like this.

id |    date    | user_id
1  | 2013-02-02 |  1
2  | 2013-02-02 |  2
3  | 2013-02-02 |  5
4  | 2013-02-03 |  6
5  | 2013-02-03 |  7
6  | 2013-02-03 |  1

Now I want to select records for only new user for a certain date or time frame. For example, if I select date=date('2013-02-03'), I need only the records which are for new users i.e. record id 4 and 5 and should not return 6 as the user already have a record.

Simply, it should only return records of users which have no previous records on their user_id. I tried writing queries with not in operator, but I don't know how to give parameter for date less then the current record date.

[EDIT]

This is where I reached and where I am stuck.

select id from mytable where date=date('2013-02-03') and user_id not in (select user_id from mytable where date<date('2013-02-03'))

This works fine when for only one exact date. But when it comes to time frame, say for a month, I can't figure out how to get the inner query where date should be the date of record for that particular user.

[EDIT]

Tried this query with using having clause. Its returning empty result.

select id, date, user_id from mytable having min(date(date))>=date("2013-01-02") and min(date(date))<=date("2013-02-02")

ok I got the problem with this query. Its check min(date) out of over all records. How can I make this look for min record for each user_id?

Upvotes: 1

Views: 490

Answers (1)

peterm
peterm

Reputation: 92795

Let's say you have 

+----+------------+---------+
| id | date       | user_id |
+----+------------+---------+
|  1 | 2013-02-02 |       1 |
|  2 | 2013-02-02 |       2 |
|  3 | 2013-02-02 |       5 |
|  4 | 2013-02-03 |       6 |
|  5 | 2013-02-03 |       7 |
|  6 | 2013-02-03 |       1 |
|  7 | 2013-02-04 |       6 | <-- additional record for user_id 6
+----+------------+---------+

And you are trying to get new users in interval 2013-02-03 - 2013-02-04

SELECT MIN(id) id, MIN(`date`) `date`, `user_id`
  FROM (
SELECT * 
  FROM mytable
 WHERE `date` BETWEEN DATE('2013-02-03') AND DATE('2013-02-04')
   AND user_id NOT IN (SELECT DISTINCT user_id 
                         FROM mytable 
                        WHERE `date` < DATE('2013-02-03'))
       ) t
 GROUP BY user_id

That will be your output:

+------+------------+---------+
| id   | date       | user_id |
+------+------------+---------+
|    4 | 2013-02-03 |       6 |
|    5 | 2013-02-03 |       7 |
+------+------------+---------+

Here is working sqlfiddle

Upvotes: 2

Related Questions