I have a snapshot of our Orders table from Jan.1 2010 to Jan. 1 2011.
What we need is to look at the time difference between orders compared to a year ago…. If, on this date last year at this time (or closest to it), we had 2 orders within 25 minutes, we need to at least have the same scenario today. If the time between orders is greater this year, then the query returns a result.
Example: On 1/3/2010 at 9:54:32 AM, we had an order. The next order on that date was at 10:20:14 AM. I am thinking that the query would run every 15 minutes (via a form timer I already have built, more later) and it would look in that snapshot table to find the closest time period to now. In the above case, the query would know that its start time is 9:54:32 AM and its end time is 10:20:14. Then, it would look at our CURRENT orders table and see if the time difference (in that case roughly 26 minutes) between latest orders is greater than what is in the snapshot table. If it is, a result is returned.
I don’t care what result the query returns… I have a stored procedure which gets run every 15 minutes on a form timer. That procedure checks the result set of the query. If the query is NOT empty, the procedure sends an email notifying a staff member that “Frequency of orders has decreased”.
I know this is convoluted… please ask for any clarification you may need. Thanks very much.