Getting records by date is multiples of 30 days

Question

I have the following query to get appointments that need remind once a month if they are not done yet. I want to get records with 30, 60, 90, 120,etc... in the past from the current date.

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

is there another way not to use DATEDIFF to achieve this? I want to increase the performance of this query.

Answer 1

Ok, lets all put the dates and date-diff aside for a moment. Looking at the question, the person is trying to look for all appointments in the past that dont necessarily have another in the future. Such as doing a FOLLOW-UP appointment with a Dr. "Come back in a month to see where things change". This points me to thinking there is probably some patient ID in the table of appointments. So this probably turns the question to looking at the past 30, 60 or 90 days ago to see if there was a corresponding appointment scheduled in the future. If already scheduled, the patient does not need a call reminder to get into the office.

That said, I would start a bit differently, get all patients that DID have an appointment within the last 90 days, and see if they already have (or not) a follow-up appointment already on the schedule for the follow-up. This way, the office person can make contacts with said patients to get on the calendar.

start by getting all maximum appointments for any given patient within the last 90 days. If someone had an appointment 90 days ago, and had a follow-up at 59 days, then they probably only care about the most recent appointment to make sure THAT has the 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, from this fixed list and beginning date, all we care is, how many days to current is there last appointment. IS it X number of days? Just use datediff and sort. You can visually see the how many days. By trying to break them into buckets of 30, 60 or 90 days, just knowing how many days since the last appointment is probably just as easy as sorting in DESCENDING order with the oldest appointments getting called on first, vs those that just happened. Maybe even cutting off the calling list at say 20 days and still has not made an appointment and getting CLOSE to the expected 30 days in question.

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, having an answer just for the direct question doesnt get the correct need. Hopefully I am more on-target with the desired ultimate outcome needs. Again, the above implies joining to the patient table for follow-up call purposes to schedule an appointment.