Help with query for inactive patients

[Nate]

I want a query to search for patients that we can inactivate and pull charts for. I have the following query but it includes patients that are also inactive and we have already pulled their charts.

Patients not seen since XYZ Date

SELECT patient.LName, patient.FName, patient.Address, patient.Address2, patient.City, patient.State, patient.Zip, procedurelog.procdate
FROM patient,procedurelog
WHERE procedurelog.patnum=patient.patnum
AND procedurelog.procstatus = '2'
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2008-01-01'

Thanks for any help

[atd]

These are the patient.PatStatus values:
Patient: 0
NonPatient: 1
Inactive: 2
Archived: 3
Deleted: 4
Deceased: 5
So I just added patient.PatStatus=0 to your query below.

SELECT patient.LName, patient.FName, patient.Address, patient.Address2, patient.City, patient.State, patient.Zip, procedurelog.procdate
FROM patient,procedurelog
WHERE procedurelog.patnum=patient.patnum
AND procedurelog.procstatus = '2'
AND patient.PatStatus=0
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2008-01-01'

[Jorgebon]

I would also add that to get the correct date of the patient's last visit, you should make a small change in the SELECT line as follows:

SELECT patient.LName, patient.FName, patient.Address, patient.Address2, patient.City, patient.State, patient.Zip, Date_Format(MAX(procedurelog.ProcDate),'%m/%d/%y') as 'Date Last Visit'
FROM patient,procedurelog
WHERE procedurelog.patnum=patient.patnum
AND procedurelog.procstatus = '2'
AND patient.PatStatus=0
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2008-01-01'

You could also add a line at the end to order the list alphabetically with:
ORDER BY patient.LName, patient.FName

Jorge Bonilla, DMD