I want to retrieve a list of patients currently in the hospital. I can do
this (I think) by comparing admit dates with departure dates-every patient
who has an admit_date that is later than their most recent departure date is
here. However, the SQL I've cobbled together is bad:



I've tried all the derivations of the following I can think of, and nothing
works:



select p.firstname, p.lastname, a.admit_date, a.ADMISSION_ID,
a.ADMIT_ORDINAL, d.departure_date

from patient_admissions a

join departures d on d.admission_id = a.ADMISSION_ID

join patients p on p.PATIENT_ID = a.admission_id

where max(a.admit_date) > max(d.DEPARTURE_DATE)



--if I replace "where" with "having," that also fails.



Clay Shannon,

Dimension 4 Software





[Non-text portions of this message have been removed]