sqlaggregates

SQL last 6 months visits

Purpose of the report: Identify patients who did not have dental cleanings in the last 6 months

What would be the best approach to write a sql script?

Patients table

patient_id patient_name
11 Jason Strong
22 Ryan Smith
33 Casey Hammer

Visits table

v_id patient_id reason_visit date_of_visit
1 11 medical 01/01/2021
2 22 dental cleaning 11/10/2020
3 22 annual 01/01/2021
4 11 dental cleaning 5/10/2021
5 11 annual 5/1/2021

Expected

patient_id patient_name
22 Ryan Smith
33 Casey Hammer

Casey is on the list because she is not in the visits table meaning she never received a cleaning from our office.

Ryan Smith is on the list because it is time for his cleaning.

I was also thinking what if the patient did not have an appointment in the last 6 months but had an future appointment for dental cleaning. I would want to exclude that.

Solution

  • in postgresql:

    select * from Patients p
where not exists (
    select 1 from Visits v
    where v.patient_id = p.patient_id
    and reason_visit = 'dental cleaning'
    and date_of_visit < now() - interval '6 month'
)

    in sql server replace now() - interval '6 month' with dateadd(month, -6,getdate())

    in mysql date_add(now(), interval -6 month)