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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pkromerAuthor Commented:
Ok, Ill try that. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.