asked on

MySQL query datefunction analyse who hasn't improved in 6 days

I have a database which contains player logs.

The database is mysql and contains a day, month and year.

I'm looking for a query so that i could see who hasn't made any progress in 6 day's or more. (this means that the points has stayed the same)

Who can give me the correct query?

I now have a query that displays the "players" and shows the points.
Also, a date is created with the year, month and day

SELECT p.playername, (CONCAT_WS('/',ph.year, ph.month,ph.day)) as datum, ph.points
FROM players_history ph, players p, galaxytool c
WHERE ph.player_id = p.id
AND c.player_id = p.id
AND ph.points < 5000
ORDER BY p.playername ASC
, datum
;

Open in new window

racek

SELECT p.playername, 
       MAX(CONCAT(ph.year,'/',ph.month,'/',ph.day)) as max_datum 
FROM players_history ph
JOIN  players p ON ph.player_id = p.id 
 AND ph.points < 5000
JOIN  galaxytool c ON c.player_id = p.id
GROUP BY 1
HAVING max_datum >= '2009-04-23';

Open in new window

NerdsOfTech

SELECT p.playername,
MAX(CONCAT(ph.year,'/',ph.month,'/',ph.day)) as max_datum
FROM players_history ph
JOIN players p ON ph.player_id = p.id
AND ph.points < 5000
JOIN galaxytool c ON c.player_id = p.id
GROUP BY 1
HAVING max_datum >= SUBDATE( CURRENT_DATE, INTERVAL 6 DAY ) ;

ASKER CERTIFIED SOLUTION

NerdsOfTech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NerdsOfTech

Split points if you were looking for the above dynamic query. I used racek's solution and modified it with SUBDATE

Thanks.

SOLUTION

racek

membership

This solution is only available to members.