Query to find routines that should have run since timestamp

Databases
1

Hi All

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:

[B]inspection_routine[/B]
inspection_routine_pk
inspection_name
location_fk

example data:
1, Daily Routine, 7
2, Weekly Routine, 7
3, Monthly Routine, 7
4, Adhoc Routine, 7

[B]inspection_routine_schedule[/B]
inspection_routine_schedule_pk
inspection-routine_fk
complete_by_time
frequency
day_no
adhoc_date

example data:

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

I get the routines for the current day fairly easily:

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
	AND
	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

Ignore locations table for now that is not an issue

What I need to be able to to is work out those routines that should have been completed but that have not

So…

I have an an inspection_record table:

[B]inspection_record[/B]
inspection_record_pk
inspection_routine_fk
timestamp_started
timestamp_completed
staff_fk

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

Matt Houldsworth