Need to fill in empty date holes for missing data

[thepixel]

I'm trying to output a grid view of year/month data from a table using this query:

SELECT
	COUNT(`id`) AS `total`, DATE_FORMAT(`report_date`,'%Y-%m') AS `group_date`,
	DATE_FORMAT(`report_date`,'%Y') AS `report_year`, DATE_FORMAT(`report_date`,'%m') AS `report_month`,
	DATE_FORMAT(`report_date`,'%M') AS `disp_month`
FROM `mhl_reports`
WHERE `deleted` IS NULL AND YEAR(`report_date`) >= '2001' AND YEAR(`report_date`) <= '2009'
GROUP BY `group_date`
ORDER BY `report_month` ASC, `report_year` DESC;

So this data will be displayed in a grid with years 2009, 2008, 2007... to 2001 across the top, then Jan-Dec down left side, then the `total` value in the grid spot. To make programming this grid much easier, I was wondering if there was a way to have this query add rows for missing months/years between the range (should go from 1-2009 to 12-2001)

so instead of getting
2009-01
2008-01
2007-01
2005-01 .. and so on for jan.
2008-02 .. (note jan 09 is missing)
2008-03

i need to get
2009-01
2008-01
2007-01
2006-01 <== added
2005-01 .. and so on for jan.
2009-01 <== added
2008-02
2008-03 .. and so on...

Does that make sense?

 

[r937]

to make programming the grid easier, just pull only the data that you actually have from the database, and fill in the holes with your application language, asp or php or whatever you're using

it's a lot easier that way

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon