Query to find routines that should have run since timestamp
I have a conundrum that I am struggling with. I am creating a schedule system with recurring 'routine' types - Daily, Every Week on Day X, Every Month on Day X and Adhoc on specific day.
I have The following tables:
1, Daily Routine, 7
2, Weekly Routine, 7
3, Monthly Routine, 7
4, Adhoc Routine, 7
I get the routines for the current day fairly easily:
1, 1, 11:00:00, Daily, null, null
2, 2, 13:00:00, Weekly, 4, null
3, 3, 15:00:00, Monthly, 23, null
4, 4, 16:00:00, Adhoc, null, 2013-01-27
Ignore locations table for now that is not an issue
SELECT inspection_routine.inspection_routine_pk, inspection_routine.inspection_name, inspection_routine_schedule.complete_by_time
from inspection_routine, inspection_routine_schedule, locations
WHERE inspection_routine.inspection_routine_pk = inspection_routine_schedule.inspection_routine_fk
inspection_routine.location_fk = locations.location_pk
AND locations.location_pk = '$location_fk'
AND (inspection_routine_schedule.frequency = 'Daily'
OR (inspection_routine_schedule.frequency = 'Weekly' AND inspection_routine_schedule.day_no = DAYOFWEEK(NOW()) )
OR (inspection_routine_schedule.frequency = 'Monthly' AND inspection_routine_schedule.day_no = DAY(NOW()) )
OR inspection_routine_schedule.adhoc_date = CURDATE()
ORDER BY inspection_routine_schedule.complete_by_time ASC
What I need to be able to to is work out those routines that should have been completed but that have not
I have an an inspection_record table:
My idea is that each time I run the script I will enter a timestamp into a cron_log table, thus I need to be able to show all the routines that should have been completed since the last_ran_timestamp
I know I can get a between last_ran_timestamp and NOW(), but how can I translate my data held against schedules into timestamps so that I can compile a list of schedules that should have been run since a timestamp even though that simestamp maybe 2 days ago, thus giving two records for the daily routine for example
Sorry it is getting complicated, I am in too deep and probably over complicating matters. I could just rely on the cron running daily, thus the query above works and I can check to see if the inspection has been carried out, however, if that query rails to run (cron fails, server down, etc etc) we would not be able to check what should have happened yesterday.
Thanks for any pointers in advance