Reminder emails

Question

Reminder emails

simflex-897410

Hall of Fame

Points: 3881
More actions
June 29, 2015 at 7:42 am

#305411

Greetings experts,
We have a difficult requirement.
When records are initially created, they are given statuses of New, Pending, Closed
By default, the status is New.
If no action has been taken after 15 days since the records were created, users are sent a reminder.
I have this part working.
The problem is that as long as the status is not equal to closed, start sending reminders every 10 days.
I am not sure how to approach this.
Any ideas?
Here is what I am working with:
SELECT [ID] , [Name], [Email],
Status
FROMmyTable
WHERE Status_Name != 'Closed' AND Status_Name IS NOT NULL and Status_Name = 'New' and Status='Pending'
AND CONVERT(Char,DateAdd(day,-15,getdate()),101) = CONVERT(Char,Date_Entered,101)

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

assuming the data is right, your query works for me;

do you have a seperate table that tracks each email you sent out, asswociated to the unique item/ID? you need to track outbound messages.

maybe you want to test that the date is older than 15 days, and a message is not sent yet, rahtehr than a hardcoded date match?

ie AND Date_Entered < DateAdd(day,-15,getdate()) AND NOT EXISTS(SELECT ID FROM EmailHistory WHERE DateSent > DateAdd(day,-10,getdate())

IF OBJECT_ID('tempdb.[dbo].[#myTable]') IS NOT NULL

DROP TABLE [dbo].[#myTable]

SELECT 1 AS [ID] ,

'New Item' AS [Name],

'fake@somedomain.com' AS [Email],

'Pending' AS [Status],

'New' AS Status_Name,

DATEADD(dd,-15,getdate()) AS Date_Entered

INTO #myTable

SELECT [ID] , [Name], [Email],

Status,

CONVERT(Char,DateAdd(day,-15,getdate()),101) AS TheDate,

CONVERT(Char,Date_Entered,101) As TargetDate

FROM#myTable

WHERE Status_Name != 'Closed'

and Status_Name = 'New'

and Status='Pending'

AND CONVERT(Char,DateAdd(day,-15,getdate()),101) = CONVERT(Char,Date_Entered,101)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Hi Lowell, yes, I do have a separate table that tracks emails called Notifications.

Each time a reminder is sent, a field called Sent is updated from No to Yes.

This way, the email doesn't get sent again until new records are inserted into this table.

I am reviewed the query and I understand using 15 days to process records not closed after 14 days but the reminder needs to go out after 10 days.

So, initially, send reminders after 14 days and status is not closed. After this initial reminder, start sending reminders every 10 days.