SQL - Patient with an office visit one year before the most recent one

Question

I'm trying to write a query that brings back patients who have had an office visit at least a year before the most recent one. The various ways I'm thinking of get all twisted up in my head, so I was wondering if anyone out there can see an obvious way to write it that I'm missing. Don't worry too much about the exact syntax and naming -- I should be able to translate it. :-)

Thanks!

Answer 1

select distinct * from PatientHistory
where lastVisit = date_sub(lastVisit,interval 1 year)

Answer 2

Use:

SELECT ph.*
  FROM PATIENT_HISTORY ph
 WHERE ph.visit_date >= (SYSDATE - 365)

...or:

SELECT ph.*
  FROM PATIENT_HISTORY ph
 WHERE ph.visit_date >= ADD_MONTHS(SYSDATE, -12)

ADD_MONTHS is self-explanatory. In Oracle, SYSDATE returns the current date and time, and you can manipulate Oracle DATE (includes time) by adding/subtracting a number (which is interpreted as the number of days).

For patients with a record that is a year or more in the past:

SELECT a.*
  FROM PATIENT_HISTORY a
 WHERE EXISTS(SELECT NULL
                FROM PATIENT_HISTORY b
               WHERE b.visit_date <= ADD_MONTHS(a.visit_date, -12))

Answer 3

If you want to determine whether a patient has been treated for at least a year (which your comment makes it sound like) then couldn't you do a query like:

SELECT *
FROM Patients p
WHERE 
    DATEDIFF(d, 
             (SELECT MIN(AppointmentDate) FROM Appointments a WHERE a.PatientID = p.PatientID),
             (SELECT MAX(AppointmentDate) FROM Appointments a WHERE a.PatientID = p.PatientID)) >= 365

I don't know if that syntax will work offhand, but the concept is to say, "Return only those patients where the number of days since their earliest and most recent appointments is at least 365 days.

Answer 4

It's fairly straight to do it with WINDOW FUNCTIONS in Oracle.

If your VISITS table is something like:

create table visits (
    patient_id number,
    visit_date date,
    ....
);

You can use this simple query:

select patient_id, visit_date, 
       lead(visit_date, 1, null) 
           over (partition by patient_id order by visit_date) as next_visit_date
  from visits;

And will get in one row, visit_date value, and following visit_date value for the same patient.

Then with a simple where, you can check for your one year condition.