CODEWITHSUNDEEP
mysqlsql
Jorg Ancrath
Jorg Ancrath

Reputation: 1447

Select rows with HAVING after a given date

Having a structure like so:

+----+------------+--------+-----------+
| id |    date    | userid |  status   |
+----+------------+--------+-----------+
|  1 | 2013-06-05 |      1 | validated |
|  2 | 2013-06-05 |      2 | validated |
|  3 | 2013-06-06 |      2 | pending   |
|  4 | 2013-06-07 |      1 | validated |
|  5 | 2013-06-08 |      1 | validated |
|  6 | 2013-06-08 |      1 | validated |
|  7 | 2013-06-09 |      1 | validated |
+----+------------+--------+-----------+

If I want to select users with 5 validated statuses, I can do:

 SELECT userid, COUNT(status) as valid
 FROM table1 
 WHERE status="validated"
 GROUP BY userid
 HAVING valid=5

Now I want to up the complexity of this query, I want to select users that have 5 validated rows starting from a given date:

 SELECT userid, COUNT(status) as valid
 FROM ladder_offers_actions 
 WHERE status="validated"
 AND date > "2013-06-06"
 GROUP BY userid
 HAVING valid=5

This will of course return 0 users with the example given above, this is because it's only looking at validated entries after 2013-06-06 (4).

I want to select users that only have 5 validated entries after a given date... example:

User 1 has 4 validated rows before 2013-06-06 and 1 validated row after 2013-06-06 - this user should be included in the select

User 2 has 3 validated rows before 2013-06-06 and 2 validated row after 2013-06-06 - this user should be included in the select

User 3 has 5 validated rows before 2013-06-06 - this user should not be included in the select.

User 4 has 5 validated rows after 2013-06-06 - this user should be included in the select.

Hopefully this is clear enough, essentially I only want users that that have 5 validated rows after a certain date, but include the rows before that date if the user didn't yet have 5 validated rows.

Upvotes: 0

Views: 129

Answers (2)

PeterRing
PeterRing

Reputation: 1797

Well to simplify your problem, you want to select someone if she has 5 validate and she has any validate after a certain date.

Then you can use:

 SELECT userid, COUNT(status) as valid
 FROM ladder_offers_actions 
 WHERE status="validated"
 and userid in 
    (SELECT t2.userid 
    FROM ladder_offers_actions t2
    WHERE t2.date > "2013-06-06")
 GROUP BY userid
 HAVING valid=5

Upvotes: 0

Matt
Matt

Reputation: 3363

If I understand correctly what you are asking, how about using a subquery? Something like:

SELECT * from (
  SELECT userid, COUNT(status) as valid, MAX(date) as lastdate
  FROM ladder_offers_actions 
  WHERE status="validated"
  GROUP BY userid
  HAVING valid=5
) x WHERE x.lastdate > '2013-06-06'

Upvotes: 3

Related Questions