In an application I want to do a comparative ‘last 12 months’, and I have the following SQL to try and achieve that in MySQL. This works where all months have entries recorded, but I also want to include the ‘empty months’. Anyone have some suggestions on how to ‘capture/include’ the empty months.
I guess I could do it all programatically by brute force, i.e. work out 12 months ago, and step back through to present day, and do a month at a time, but wondering if it can all be done is SQL for speed and tidyness with the SQL working out what month was 12 months ago and working forward.
The sql currently looks like this
SELECT
DATE_FORMAT(Entered, '%b') AS MONTH,
DATE_FORMAT(Entered, '%Y') AS YEAR,
SUM(Time_Minutes) AS total
FROM
rep_copy
WHERE
rep_copy.Entered <= NOW() AND
rep_copy.Own_Time = '1' AND
rep_copy.Entered >= Date_add(Now(), INTERVAL - 11 MONTH) AND
rep_copy.Code = '52'
GROUP BY
DATE_FORMAT(Entered, '%Y-%m')
This returns the following (months with records in).
"Jul" "2017" "260"
"Aug" "2017" "855"
"Jan" "2018" "175"
"Feb" "2018" "135"
"Mar" "2018" "335"
"Apr" "2018" "170"
"May" "2018" "180"
"Jun" "2018" "30"
What I would actually like is
"Jul" "2017" "260"
"Aug" "2017" "855"
"Sep" "2018" "0"
"Oct" "2018" "0"
"Nov" "2018" "0"
"Dec" "2018" "0"
"Jan" "2018" "175"
"Feb" "2018" "135"
"Mar" "2018" "335"
"Apr" "2018" "170"
"May" "2018" "180"
"Jun" "2018" "30"
As always pointers and suggestions welcomed.