JohnH JohnH - 4 months ago 12
SQL Question

MySQL show 12 month interval without first partial month

I want to list the scores, by month, for something that happened over the last 12 months. I noticed my query below was combining the results of the first partial month with the results of the last partial month. That is, my July report combined July 9-31, 2015 with July 1-8 2016 (now is July 8). I only want the latest month to represent the latest year. Here is what I was using...

$query = "SELECT record_id,
time_scored,
MONTH(time_scored) as month_added,
score, comment
FROM records
WHERE score IS NOT NULL AND
time_scored >= DATE_SUB(curdate(),INTERVAL 12 MONTH)
ORDER BY time_scored DESC";


Any help would be appreciated. Thanks!

Answer

Subtract 12 months from next months 1st day

$query = "SELECT record_id, 
   time_scored, 
   MONTH(time_scored) as month_added, 
   score, comment 
FROM records 
WHERE score IS NOT NULL AND 
   time_scored >= DATE_SUB(DATE_ADD(subdate(curdate(), (day(curdate())-1)), INTERVAL 1 MONTH),INTERVAL 12 MONTH) 
ORDER BY time_scored DESC";
Comments