CODEWITHSUNDEEP
sqloracle-databaseoracle10g
SarekOfVulcan
SarekOfVulcan

Reputation: 1358

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

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!

Upvotes: 1

Views: 857

Answers (4)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332581

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))

Upvotes: 3

Scott Mitchell
Scott Mitchell

Reputation: 8759

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.

Upvotes: 1

IWriteApps
IWriteApps

Reputation: 993

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

Upvotes: 1

Related Questions