0

Given a table containing 'CompanyName', 'ContractExpirationDate', and 'EmailAlertAddress' where there may be more than one ContractExpirationDate on the same day, i.e. More than one company's contract may expires on the same day.

And, each company has a different address for EmailAlertAddress. I.e. Company ABC alerts go to ABC@domain.com; Company XYZ alerts go to XYZ@domain.com.

Company ABC     08/13/2016     ABC@domain.com
Company DEF     11/31/2017     DEF@domain.com
Company GHI     12/31/2017     GHI@domain.com
Company KLM     01/31/2018     KLM@domain.com
Company NOP     02/31/2018     NOP@domain.com
Company XYZ     08/13/2016     XYZ@domain.com

I need to check all the records daily for any that are expiring in 90, 60, and 30 days. (Since I'll check daily and I only want the alert to go out one time for each period I'll use "WHERE ExpirationDate = CONVERT(VARCHAR,GetDate() + 90, 101)" so "=" not ">=" Just clarifying there.)

What I can't puzzle out is how to send the alert to only the appropriate email address for each company if more than one expire on the same date.

Say, step through the ones expiring that day and send alert ABC for Company ABC to ABC@domain.com and send alert XYZ for Company XYZ to XYZ@domain.com, say 90 days before the day they both expire.

jmq
  • 10,110
  • 16
  • 58
  • 71
David Megnin
  • 79
  • 1
  • 1
  • 10
  • 3
    You could start by looking up cursors or while loops in T-SQL. Both are supported by SQL Server. – R. Richards Jul 13 '16 at 21:11
  • I would use a trigger to fire a stored procedure that executes sp_send_dbmail for each record. Then update a field in the table to processed or something like that. – briskovich Jul 13 '16 at 21:28
  • I was thinking that I'd set up a Job to run a stored procedure with something like this [see two comments down] every day and when an expiration date met the +90 days from today criteria execute sp_send_dbmail for each record (if more than one) More than one on the same day will be rare, but it will happen. – David Megnin Jul 14 '16 at 15:17
  • [how do you add a 'new line' to these comments???] – David Megnin Jul 14 '16 at 15:18
  • SELECT email01, email02, email03, email04, email05, expirationdate, Company, CONVERT(VARCHAR,GetDate(), 101) AS 'for reference only', Company + ' expires in 1 day.' AS [Subject] FROM [Contracts].[dbo].[Contracts] WHERE ExpirationDate = CONVERT(VARCHAR,GetDate() + 90, 101) – David Megnin Jul 14 '16 at 15:18

0 Answers0