Auto Email based on rule - Is it possible?

Question

Auto Email based on rule - Is it possible?

wrightyrx7

Old Hand

Points: 312
More actions
July 20, 2016 at 8:40 am

#328019

Hi all,
Just getting to know SQL Server and found information all over Google relating to sending emails via SQL Server.
However, what i would like is a scheduled email that runs once a day and send emails based on a rule.
It checks a Table/View within the database which contains an employee's: -
-START DATE
-MANAGERS EMAIL ADDRESS
The rule is if the employee has been here 3, 6 or 9 months on the day the schedule runs it emails the manager.
Basically its to remind managers of the employees probationary periods.
Appreciate any help or guidance.
Thanks in advance
Chris

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

wrightyrx7 (7/20/2016)
Hi all,
Just getting to know SQL Server and found information all over Google relating to sending emails via SQL Server.
However, what i would like is a scheduled email that runs once a day and send emails based on a rule.
It checks a Table/View within the database which contains an employee's: -
-START DATE
-MANAGERS EMAIL ADDRESS
The rule is if the employee has been here 3, 6 or 9 months on the day the schedule runs it emails the manager.
Basically its to remind managers of the employees probationary periods.
Appreciate any help or guidance.
Thanks in advance
Chris

If someone is hired on Nov 30th, 2011, what is considered to be 3, 6, and 9 months from then according to your rules?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wrightyrx7 Old Hand Points: 312 More actions · Answer 2

Jeff Moden (7/20/2016)
If someone is hired on Nov 30th, 2011, what is considered to be 3, 6, and 9 months from then according to your rules?

Hi Jeff,

Many thanks for your reply.

This is a good expample because the 3 month period would be in Feb 12 but Feb doesn't have 30 days.

If i was calculating it in Excel it would just be the results below which would be fine. As long as everyone is treated the same i cannot see the company having a problem.

3 month - 01/03/2012 (=DATE(YEAR("30/11/2011"),MONTH("30/11/2011")+3,DAY("30/11/2011"))

6 month - 30/05/2012 (=DATE(YEAR("30/11/2011"),MONTH("30/11/2011")+6,DAY("30/11/2011"))

9 month - 30/08/2012 (=DATE(YEAR("30/11/2011"),MONTH("30/11/2011")+9,DAY("30/11/2011"))

Hope this helps.

Thanks again for your reply

Chris

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Or you could go with 13, 26 and 39 weeks, to make it more likely that the manger will be informed on a working day. Start with a query like this:SELECT

EmployeeName

,ManagerName

,ManagerEmail

FROM EmployeeView

WHERE StartDate IN (

DATEADD(week,-39,CAST(CURRENT_TIMESTAMP as date))

,DATEADD(week,-26,CAST(CURRENT_TIMESTAMP as date))

,DATEADD(week,-13,CAST(CURRENT_TIMESTAMP as date))

)

You can then loop through the result set and use sp_send_dbmail to send an e-mail to each manager for each employee in turn. You could make it more sophisticated by adding a ProbationNotificationSent column to the employee table so that you can run the process more than once in a day without the same e-mail being sent twice.

John

wrightyrx7 Old Hand Points: 312 More actions · Answer 4

John Mitchell-245523 (7/21/2016)
Or you could go with 13, 26 and 39 weeks, to make it more likely that the manger will be informed on a working day. Start with a query like this:SELECT EmployeeName ,ManagerName ,ManagerEmail FROM EmployeeView WHERE StartDate IN ( DATEADD(week,-39,CAST(CURRENT_TIMESTAMP as date)) ,DATEADD(week,-26,CAST(CURRENT_TIMESTAMP as date)) ,DATEADD(week,-13,CAST(CURRENT_TIMESTAMP as date))
)
You can then loop through the result set and use sp_send_dbmail to send an e-mail to each manager for each employee in turn. You could make it more sophisticated by adding a ProbationNotificationSent column to the employee table so that you can run the process more than once in a day without the same e-mail being sent twice.
John

Hi John,

Using weeks is a great idea and I will put this across to my manager. And also the ProbationNotificationSent is a good idea which i had not thought about, we was just going to schedule it to run once a day.

I have not done a LOOP in SQL yet as i only have experience with VB.net, hopefully its not to difficult :/

Thanks again John

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

wrightyrx7 (7/21/2016)
Jeff Moden (7/20/2016)
If someone is hired on Nov 30th, 2011, what is considered to be 3, 6, and 9 months from then according to your rules?
Hi Jeff,
Many thanks for your reply.
This is a good expample because the 3 month period would be in Feb 12 but Feb doesn't have 30 days.
If i was calculating it in Excel it would just be the results below which would be fine. As long as everyone is treated the same i cannot see the company having a problem.
3 month - 01/03/2012 (=DATE(YEAR("30/11/2011"),MONTH("30/11/2011")+3,DAY("30/11/2011"))
6 month - 30/05/2012 (=DATE(YEAR("30/11/2011"),MONTH("30/11/2011")+6,DAY("30/11/2011"))
9 month - 30/08/2012 (=DATE(YEAR("30/11/2011"),MONTH("30/11/2011")+9,DAY("30/11/2011"))
Hope this helps.
Thanks again for your reply
Chris

The next question would be, do you want to send emails on weekends and holidays?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wrightyrx7 Old Hand Points: 312 More actions · Answer 6

Hello 🙂

Thanks for your reply I didn't think anyone was coming back to me on this.

Yes emails on weekends are fine the managers can just pick them up when they come in on the Monday

Chris