Retrieve missing dates from database via MySQL

Question

Hi guys,

So I have a table where I collect data for the jobs that I do. Each time I create a job I assign it a date. The problem with this is the days I don't have jobs aren't stored in the database therefore when I graph my data I never see the days that I had zero jobs.

My current query looks like this:

SELECT job_data_date, SUM(job_data_invoice_amount) as job_data_date_income 
FROM job_data 
WHERE job_data_date >= '2010-05-05' 
GROUP BY job_data_date 
ORDER BY job_data_date;

The output is:

|   job_data_date   |   job_data_date_income    |
|   2010-05-17      |   125                     |
|   2010-05-18      |   190                     |
|   2010-05-20      |   170                     |

As you can see from the example output the 2010-05-19 would not show up in the results because it was never stored there.

Is there a way to show the dates that are missing?

Thank you,

Marat

Answer 1

There's no reasonable to do this using pure SQL (on MySQL, at least) without creating a table with every date ever devised. Your best option is to alter the application that's using the results of that query to fill in the holes itself. Rather than graphing only the values you got, construct your own set of values with a simple loop (counting up one day at a time, and filling in values from the query wherever they're available).

Answer 2

One idea is that you could have a table with all of the dates in it that you want to show and then do an outer join with that table.

So if you had a table called alldates with one column (job_data_date):

SELECT ad.job_data_date, SUM(job_data_invoice_amount) as job_data_date_income 
FROM alldates ad left outer join job_data jd on ad.job_data_date = jd.job_data_date 
WHERE ad.job_data_date >= '2010-05-05' 
GROUP BY ad.job_data_date 
ORDER BY ad.job_data_date;

The down side is that you would need to keep this table populated with all of the dates you want to show.