Email notification

[alsjka]

Hi I have a question. What (or how) would be the best way to do an email notification like this.

I have a date I do a datediff against. an insurance expire date and if the date is within the next 30 days of NOW() an email gets sent to the person in charge.

But the problem is the dates keep getting queried daily so the email will keep getting sent until the date is not 30 days from NOW().

What would the best work around for this? I only need the email to go out once and then the ideally the person should update the insurance expire date. But until the expire date gets updated emails get sent.

Thank You

 

[SidianConsulting]

How about a BIT field (1 or 0) called wasNotified? Update it to 1 (TRUE) after the notification email goes out.

When you select the customers to email notifications to, do a WHERE wasNotified = 0 in your query to weed out the people who were notified already.

-Tek

 

[cf59102]

Good suggestion Tek - another way could be is that set a value for a particular date (either exactly 30 or maybe 29 if you want to give a leeway of one day) and then send the email out only at that particular day - that way they get sent within the time limit you want but do not get sent every day.

You could then run a comparison for 30, 15 7 and 1 days - that way you can catch the procrastinators with the 15, 7 and 1 day cycle! And because you know the folks that receive these emails have received the 30 or earlier alert - you can adjust the text as appropriate reminders. Of course, you would have to adjust for the first iteration of this code if you are working with an existing dataset.

HTH

 

[alsjka]

Yeah that's the way I was leaning.. When would the bit get updated? Would it be better to update the bit when the user updates the expire date?

I have it now so that the email keeps getting sent until the user clicks the link in the email setting the bit to zero.

But if the user never updates the expire date an email will never get sent again.

I think I am on the right direction now. Thank You for your input.

 

[SidianConsulting]

cf59102,

Maybe I am not understanding you, but...

Say I pick the 1st of each month to send out expiration reminders. I send reminders for all accounts that are due to expire within 30 days. What about the accounts that expire 31 days from the day I am sending the email out? Since any day could be an expiration date, you'll end up sending notifications out too late in a lot of cases.

-Tek

 

[alsjka]

I run a scheduled task everyday. So every record that will expire 30 days from now() will get sent out and each consecutive day and so on.

 

[cf59102]

alsjka hit the nail on the head. just do scheduled task for once a day and it shall do a direct comparison for every day.

 

[mightybs]

I'm not sure on you're actual check for the datediff, but it sounds like you're looking to see if it's 30 days or less?

If I wanted 30 day, or any other notifications, I would send the proper notificaiton once the datediff is equal to 30 days. So it sends just the one on 30 days out, and if you wanted to do it again at a closer date again, just when the datediff is equal to that specified number of days.

 

[cf59102]

alsjka:When would the bit get updated? Would it be better to update the bit when the user updates the expire date?

IMHO you might not need the bit field because you are running comparisons agains that expiry date - so if the record is renewed the expiry date would shift appropriately ahead. You might want to keep a record of the date they made the change just for safekeeping....