Daemon to monitor query and send mail conditionally in SQL Server

I've been melting my brains over a peculiar request: execute every two minutes a certain query and if it returns rows, send an e-mail with these. This was already done and delivered, so far so good. The result set of query is like 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 the user asks me to modify it so the solution so that, e.g., the first time that ID 21 is caught being more than 5 minutes old, the e-mail is sent to a particular set of recipie开发者_StackOverflow社区nts; the second time, when ID 21 is between 5 and 10 minutes old another set of recipients is chosen. So far it's ok. The gotcha for me is from the third time onwards: the e-mails are now sent each half-hour, instead of every five minutes.

How should I keep track of the status of Mr. ID = 43 ? How would I know if he has already received an e-mail, two or three? And how to ensure that from the third e-mail onwards, the mails are sent each half-hour, instead of the usual 5 minutes?

I get the impression that you think this can be solved with a simple mathematical formula. And it probably can be, as long as your system is reliable.

Every thirty minutes can be seen as 360 degrees, or 2 pi radians, on a harmonic function graph. That's 12 degrees = 1 minute. Let's take cosin for instance:

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

Where elapsed minutes is the time since the first 30 minute update was due to go out. This should be a constant number of minutes added to the value of last_update.

Since you have a two minute window of error, it will be time to transmit the 30 minute update if the the value of f(x) (above) is between the value you would get at less than one minute before or after the scheduled update. Which would be = cos(1* 12 degrees) = 0.9781476007338056379285667478696.

Bringing it all together, it's time to send a thirty minute update if this SQL expression is true:

COS(RADIANS( 12 * DATEDIFF(minutes, 
   DATEADD(minutes, constantNumberOfMinutesBetweenSecondAndThirdUpdate, last_update), 
   CURRENT_TIMESTAMP))) > 0.9781476007338056379285667478696

If you need a wider window than exactly two minutes, just lower this number slightly.