I have trouble with this query:

SELECT DISTINCT 
    r.uuid AS id,
    r.uuid,
    r.customerId
FROM 
    IF_reminders r
LEFT JOIN 
    IF_reminders_sent rs ON rs.reminderUuid = r.uuid 
                         AND rs.event = 'eventName'
WHERE 
    r.eventNameEnabled = 1      
    AND (rs.sentAt IS NULL
         OR rs.sentAt NOT BETWEEN (DATEADD(DAY, -14, '2022-05-01')) AND (DATEADD(DAY, 1, '2022-05-01')))

The date in DATEADD function is filled programmatically.

Table IF_reminders contain defined reminders of different types for the users.

Table IF_reminders_sent contain records then the reminder for particular event was sent to the user.

The query must return a list of user reminders for the event to which the reminder should be sent. If a reminder has already been sent, this user should be ignored.

The query shown above works as expected if table IF_reminders_sent does not contain any rows from the past years. If table does contain rows from past years, then user will get reminder every day in the specified date range.

How to update the query in a way that if for current year remainders for particular event not yet sent then full list will be returned but if current year has sent reminders for particular event then past years records will be ignored.

Update Tables structures. Three ... represent additional events columns but birthday and mothersday describe possible structures for all of them.

The idea of query is to filter out user reminders what the program should sent out. Program will fire function for to send out Mothersday reminders 7 days before event and send it once. While the IF_remiders_sent was empty all works OK. But then it contains records from the past year then query returns always list of reminders to be sent because sentAt for previous year is NOT BETWEEN dates specified in the query and starts spam users. If for the mothersDay event reminders for current year are not sent yet the query has output full list of users who have this reminder active. If for the current year the reminder is sent it should ignore current year records (NOT BETWEEN part of query) and now it has to ignore previous years too. How to add this condition to the query?

Sample IF_reminders_sent:

CodePudding user response：

You can define in your where clause to check the current year:

SELECT DISTINCT 
r.uuid AS id,
r.uuid,
r.customerId
FROM 
IF_reminders r
LEFT JOIN 
IF_reminders_sent rs ON rs.reminderUuid = r.uuid 
                     AND rs.event = 'eventName'
WHERE 
DATEPART(year,rs.sentAt)=DATEPART(year,getdate()) and 
r.eventNameEnabled = 1      
AND (rs.sentAt IS NULL
     OR rs.sentAt NOT BETWEEN (DATEADD(DAY, -14, '2022-05-01')) AND 
 (DATEADD(DAY, 1, '2022-05-01')))

CodePudding user response：

You need to flip the logic around. You are looking for all reminders which do not have a sent reminder since the beginning of the year.. So you need NOT EXISTS

SELECT
    r.uuid AS id,
    r.uuid,
    r.customerId
FROM 
    IF_reminders r
WHERE NOT EXISTS (SELECT 1
    FROM
        IF_reminders_sent rs
    WHERE rs.reminderUuid = r.uuid 
      AND rs.event = 'eventName'
      AND rs.sentAt >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
      AND rs.sentAt NOT BETWEEN DATEADD(DAY, -14, '2022-05-01') AND DATEADD(DAY, 1, '2022-05-01')
  )
  AND r.eventNameEnabled = 1;

Note that if you want to give a date range you should always use rs.sentAt >= SomeDateCalculation AND rs.sentAt < OtherDateCalculation rather than using functions such as YEAR(rs.sentAt) = YEAR(GETDATE())