How to send reports to different recipients based on the date

Reposted from https://www.sqlmessenger.com/docreader.html?id=559

Q: I have a duty engineer roster like the one below. I want to send some alert messages to the engineer on duty for each day based on the date. For example, send alert emails to Liam on Monday, to Noah on Tuesday, and so on. How should I configure this?

A: You can use the "Variable Recipient" of SQLMessenger to accomplish this task.

Here we use SQL Server database to demonstrate. If the roster is stored in a different type of database, then the query statement for the variable needs to be modified.

Step 1: Create a variable named "SendTo" for the task.

The value source the variable is "Read from database". The SQL statement for the variable as follows:

select email from roster where week_day = DATENAME(weekday, getdate());

Step 2: Select the variable "SendTo" created in the previous step as the recipient for the task.

When the system executes this task, it will use the SQL query of the variable to look up the email address of the engineer on duty for the day in your roster table and send the email to that address.

You can also use this method to send reports to members of your Slack workspace.

Tip: This method requires configuring the Slack App in SQLMessenger first.

• First, add a column named "slack_member_id" to the roster table to store each engineer's Slack Member ID.

• Then, create a variable named "MemberID" in the task, and use it to query the Slack Member ID of the on-duty engineer for the current day from the roster table.

• In the "Select Recipients" dialog, navigate to the "Send Via Slack" tab and select the variable "MemberID" as the Slack recipient for the task.

Alert list sent to the on-duty engineer via Slack by the task:

Note: To ensure the email body retains its formatting, we use SQLMessenger's format conversion feature to convert it to PDF before sending it to the Slack recipient.