check a MySQL table for missing records, determined by absence of records for any given date

I need to have an automated routine which looks in a MySQL table for any missing records by date field. The table should have multiple records from every single day, but something is causing some days to not get any records inserted at all. I need to be notified of these missing dates so I can investigate. Id rather do this in VB so I could set up a Windows scheduled task to run it once every 24 hours, but if need be I could do it in php and put it on our web server under a cron job. I'd highly prefer the VB route though.
pkromerAsked:
Who is Participating?
 
Chris StanyonCommented:
Rather than give you specific code implementation, here's the general idea on how to do it.

Basically, you would need to create a 'calendar' table to hold the 'valid' dates - i.e. all the dates you want to check against. You could either build this up as and when you need it (delete all records / insert the ones you want to check) or you could pre-populate it with an entire range - say a year's worth. It only needs to contain a single datetime column.

Then you would do a join between the calendar table and your actual data table, setting the WHERE clause for the start date and end date. You would also include a NULL check in the WHERE which is what gives you the missing dates. Something like this:

SELECT calTable.date
FROM calTable
LEFT JOIN dataTable ON calTable.date = dataTable.date
WHERE calTable.date BETWEEN '2018-02-01' AND '2018-02-28'
AND dataTable.date IS NULL;

Open in new window

This query assumes you have 2 tables. The calTable with column called date containing all the valid dates, and your own table, called dataTable with a column called date.

It will return a list of dates from the calendar table that don't have matching records in your data table.

Once you understand the principal of the query, implementing in PHP or VB should be fairly straight forward (I don't know VB!)
0
 
pkromerAuthor Commented:
Ok, Ill try that. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.