Send mail every N days in SQL

I need to send a warning mail 14 days before certificates valid_to date ends and also send email every 3 days from delay_days.

Table Certificates consists of:

ID(int) | cert_valid_to(date) | warn_before_days(int) | delay_days(int)
1       | '2019-09-30'        | 14                    | 3

First email I can send via this select

select datediff(day, getdate(), c.cert_valid_to) = warn_before_days
from xyz.certificates c

I need to extend my select with delay_days(int) but don’t know how yet. I think it could be done with modulo operation.

Answer

I think this should do it:

DECLARE @today AS DATE = CURRENT_TIMESTAMP

SELECT *
FROM xyz.certificates
WHERE DATEDIFF(DAY, @today, cert_valid_to) <= warn_before_days
AND (warn_before_days - DATEDIFF(DAY, @today, cert_valid_to)) % delay_days = 0

DB Fiddle with tests

Advertisement

Answer