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
Help with query for inactive patients
Re: Help with query for inactive patients
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'
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'
Re: Help with query for inactive patients
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
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
Jorge Bonilla DMD
Open Dental user since May 2005
Open Dental user since May 2005