Scratching my head on something for sometime now and I can't seem to resolve it through MySQL. I have a table that has records of events. I want to remind customers of an event every month, 7 days before it's due date. I have 3 tables called customers, events and event_reminders.
To describe what these tables do:
Customers table - a list of customers Events table - when the event was created and the day the reminder is set for Event Reminders table - A list of date/time for when the reminder was sent out
My thought process is that based on the "reminder_day" field ( 1st, 2nd, 3rd, 30th) you create a custom date, passing in the field called "reminder_day". You then do an interval of -7 days and check if this exists in the event_reminders table for every month. My SQL statement so far is below:
SELECT c.customer_name, c.customer_email, REPLACE(e.reminder_day, RIGHT(e.reminder_day,2), '') as due_day FROM customers c JOIN events e ON (c.customer_id = e.customer_id) WHERE NOT EXISTS ( SELECT 1 FROM event_reminders r WHERE MONTH(r.reminder_date) != MONTH(CURRENT_DATE()) )
The query above is checking the event_reminders table, to check if the current month record exists for an event of a customer but I need it to do a check on the due_day alias with an interval of -7 days.
I hope some genius could help me and it is clearly defined on my end. Much appreciated!