Daemon to monitor query and send mail conditionally in SQL Server -

i've been melting brains on peculiar request: execute every 2 minutes query , if returns rows, send e-mail these. done , delivered, so far good. result set of query this:

+----+---------------------+ | id | last_update         | +----+---------------------| | 21 | 2011-07-20 13:03:21 | | 32 | 2011-07-20 13:04:31 | | 43 | 2011-07-20 13:05:27 | | 54 | 2011-07-20 13:06:41 | +----+---------------------| 

the trouble starts when user asks me modify solution that, e.g., first time id 21 caught being more 5 minutes old, e-mail sent particular set of recipients; second time, when id 21 between 5 , 10 minutes old set of recipients chosen. far it's ok. gotcha me third time onwards: e-mails sent each half-hour, instead of every 5 minutes.

how should keep track of status of mr. id = 43 ? how know if has received e-mail, 2 or three? , how ensure third e-mail onwards, mails sent each half-hour, instead of usual 5 minutes?

i impression think can solved simple mathematical formula. , can be, long system reliable.

every thirty minutes can seen 360 degrees, or 2 pi radians, on harmonic function graph. that's 12 degrees = 1 minute. let's take cosin instance:

f(x) = cos(x) f(x) = cos(elapsedminutes * 12 degrees) 

where elapsed minutes time since first 30 minute update due go out. should constant number of minutes added value of last_update.

since have 2 minute window of error, time transmit 30 minute update if the value of f(x) (above) between value @ less 1 minute before or after scheduled update. = cos(1* 12 degrees) = 0.9781476007338056379285667478696.

bringing together, it's time send thirty minute update if sql expression true:

cos(radians( 12 * datediff(minutes,     dateadd(minutes, constantnumberofminutesbetweensecondandthirdupdate, last_update),     current_timestamp))) > 0.9781476007338056379285667478696 

if need wider window 2 minutes, lower number slightly.