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

LVL 7
NicoJuicyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NerdsOfTechConnect With a Mentor Technology ScientistCommented:

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 ) ;

Open in new window

0
 
racekCommented:

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

0
 
NerdsOfTechTechnology ScientistCommented:
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 ) ;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

Thanks.
0
 
racekConnect With a Mentor Commented:
as I can see you need not active players :-) ... and date has '-' ...
I am not sure that you need join with galaxytool ???

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 ) ;
0
 
NerdsOfTechTechnology ScientistCommented:
Nice.
0
 
NicoJuicyAuthor Commented:
Nice, what i've been looking for :)
0
All Courses

From novice to tech pro — start learning today.