The rewritten title is: Retrieving records in increments of 30 days

Question

I have the following query to get appointments that need to be reminded once a month (if they haven't been completed yet). I want to get records for the past 30, 60, 90, 120, etc. from the current date.

SELECT
    a.*
FROM
    appointments a
WHERE
    DATEDIFF(CURDATE(), a.appointment_date) % 30 = 0

Is there another way to achieve this without using DATEDIFF? I want to improve the performance of this query.

Answer 1

You can use the following query to compare the date of the appointment's current month to today's date.
We also test if it's the last day of the month to make an appointment at the end of the month. For example, if we have February 28th (not a leap year), we will accept the dates for that month >= 28, which is 29, 30, and 31, which would otherwise be missed.
This method suffers from the same problem as your current system, which is that weekend appointments will be missed.

select a.*
from appointements a,
(select 
  day(now()) today,
  case when day(now())= last_day(now()) then day(now()) else 99 end lastDay
) days
where d = today or d >= lastDay;

Answer 2

Okay, let's put dates and date differences aside for now. From the perspective of this question, the person is trying to find all appointments in the past and not necessarily another appointment in the future. For example, make a follow-up appointment with your doctor to “come back in a month and see if things have changed.” This got me thinking that there might be some patient ID in the appointment form. So that might shift the question to looking back 30, 60 or 90 days ago to see if there are appointments scheduled for the future. Patients do not need a phone reminder to come into the office if they already have an appointment.

That said, I would start differently and take all patients who have had an appointment within the last 90 days and see if they have (or have not) had a follow-up appointment on the follow-up schedule - upwards. This way, office staff can contact said patient to be placed on the calendar.

Start by getting all the maximum appointment volumes for any given patient over the past 90 days. If someone made an appointment 90 days ago and had a follow-up 59 days ago, they might only care about the most recent appointment to ensure there was a follow-up.

select
      a1.patient_id,
      max( a1.appointment_date ) MostRecentApnt
   from 
      appointments a1
   WHERE
      a1.appointment_date > date_sub( a1.appointment_date, interval 90 day )
   group by
      a1.patient_id

Now, starting from this fixed list and start date, all we care about is how many days until the current date is the last date. Is it X day? Just use datediff and sort. You can visually see how many days there are. By trying to split them into 30 day, 60 day or 90 day buckets, just knowing how many days since the last appointment might be as simple as sorting in descending order, calling the oldest appointments first, rather than the ones that just happened. It's even possible to cut off the call list after 20 days and still not have an appointment and be close to the expected 30 days.

SELECT
      p.LastName,
      p.FirstName,
      p.Phone,
      Last90.Patient_ID,
      Last90.MostRecentApnt,
      DATEDIFF(CURDATE(), Last90.appointment_date)  LastAppointmentDays
   FROM
      ( select
              a1.patient_id,
              max( a1.appointment_date ) MostRecentApnt
           from 
              appointments a1
           WHERE
              a1.appointment_date > date_sub( a1.appointment_date, interval 90 day )
           group by
              a1.patient_id ) Last90
      -- Guessing you might want patient data to do phone calling
         JOIN Patients p
            on Last90.Patient_id = p.patient_id
   order by
      Last90.MostRecentApnt DESC,
      p.LastName,
      p.FirstName

Sometimes, simply providing an answer to a direct question doesn't serve the right purpose. Hopefully I can be more precise with the desired end result needs. Again, the above means joining the patient form for a follow-up call to schedule an appointment.