Recently, I had to create an automatic alert for a company intranet. The final user wanted to receive an e-mail - and just one email - a month prior to an expiration date. Basically I had a list of names and a related date which represent an issue date for a document. This document expires after one year and the final user needed to receive an email alert a month before the expiration date.
It looks like an easy task, doesn't it? In the following post, we are going to see the logic behind it and how to build the alert system.
Some assumptions
As usual we need to consider some things before going into the theory behind it all.As said above, we need a table (called 'table') in our database with a date field. The date is representing our expiration date, and we call it 'exp_date'.
Then we need another field, that we can call 'email_notification'.
There will surely be other fields in our table, however, for the purpose of the following exercise, we don't really care. Let's say we have just another field, called 'name' (aside for the usual id field).
Thus, our table structure is the following:
id - int (unique)
name - varchar(255)
exp_date - smalldatetime
email_notification - numeric(18,0) - default value = 0
That is all.