Find last date from one table prior to date from another table?

[Guest]

I work in a hospital and have a table of historical outpatient data and
another table of historical inpatient data. I can link the tables using
'patient ID'. What I want to do is to identify the last outpatient
appointment date prior to each inpatient admission date. Unfortunately,
there can be multiple appts both before and after an inpatient admission.
Also there can be mutiple inpatient admissions.

Any advice much appreciated.

 

[Jeff Boyce]

If there are "multiple ...", how do YOU resolve which one to use?

 

[Guest]

Jeff
I'm not sure what you want. What I am trying to measure is the time from
referral (the last appt) to admission. If a patient is admitted as an
inpatient in June, then, if he had appts in Feb, March, Apr, July & August, I
want it to select the April appt (as the last before admission) so I can
measure the time he waited.

 

[John Spencer (MVP)]

A query something like the following might be what you want

SELECT I.PtID, I.AdmitDate,
(SELECT MAX(O.VisitDate)
FROM OutPatient as O
WHERE O.PtID = I.PtID AND
O.VisitDate <=I.AdmitDate) as PriorDate
FROM Inpatient as I