Is there a report for patients not seen in several years. When I switched to opendental I imported all of my previous patients, many that I have not seen in years. Would like a list so I can purge their charts and records.
Thanks,
cneelley
Report for patients not seen in 4 or 5 years
This query should do what you want. It will also give a count of how many patients haven't come in. You can change the date in the next to the last line as needed. Copy and paste it to your query window:
SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.LName,patient.FName,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip, MAX(procedurelog.procdate)
FROM patient,procedurelog
WHERE procedurelog.patnum=patient.patnum
AND procedurelog.procstatus = '2'
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2003-01-01'
ORDER BY patient.address, patient.address2
SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.LName,patient.FName,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip, MAX(procedurelog.procdate)
FROM patient,procedurelog
WHERE procedurelog.patnum=patient.patnum
AND procedurelog.procstatus = '2'
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2003-01-01'
ORDER BY patient.address, patient.address2
Sorry, I didn't know they had no procedures. Try this query, it will check for a first visit date and includes only those classified as "patients" (no inactive or dead patients). I am thinking that maybe they have no first visit date since they haven't come in yet from Open Dental's point of view. Hope it helps:
SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.LName,patient.FName,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip
FROM patient
WHERE datefirstvisit = '0001-01-01'
AND patstatus = '0'
ORDER BY LName, FName
SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.LName,patient.FName,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip
FROM patient
WHERE datefirstvisit = '0001-01-01'
AND patstatus = '0'
ORDER BY LName, FName
Try it anyway. I tried it by adding a patient (only the first and last name) to the database. I didn't add any procedures. This query found that patient. I also tried it by trying datefirstvisit is null. That didn't work because apparently the datefirstvisit defaults to 0001-01-01 when you create the patient record.
Can you see if there is anything else unique in the group of patients you want ? Do they have anything entered in the chart module (any previous treatment)? Do you know the exact date when these records where created in Open Dental? Are they classified as patients or as non patients in the family module?
Last edited by Jorgebon on Sun Jan 06, 2008 6:35 am, edited 1 time in total.
OK, I combined what we know about these patients,
1. no procedures in last 4 years
2. datefirstvisit = 0001-01-01
3. classified as patient in family module
I came up with this query, but if you have current patients that meet all of these conditions, they will also appear. For example, I tried it on my database and found that we have some who are not our patients, but are entered anyway because they are the subscriber in the insurance plan and we had failed to classify them as non-patient.
Give it a try:
SELECT LName,FName, Address, Address2, City, State, Zip
FROM patient
WHERE PatStatus=0
AND patient.datefirstvisit = '0001-01-01'
AND NOT EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate < CURDATE() - INTERVAL 4 YEAR)
ORDER BY LName,FName
Note: I edited this again, but we need at least something that differentiates the patients you want on the list from the insurance subscribers that are not patients
1. no procedures in last 4 years
2. datefirstvisit = 0001-01-01
3. classified as patient in family module
I came up with this query, but if you have current patients that meet all of these conditions, they will also appear. For example, I tried it on my database and found that we have some who are not our patients, but are entered anyway because they are the subscriber in the insurance plan and we had failed to classify them as non-patient.
Give it a try:
SELECT LName,FName, Address, Address2, City, State, Zip
FROM patient
WHERE PatStatus=0
AND patient.datefirstvisit = '0001-01-01'
AND NOT EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate < CURDATE() - INTERVAL 4 YEAR)
ORDER BY LName,FName
Note: I edited this again, but we need at least something that differentiates the patients you want on the list from the insurance subscribers that are not patients
Last edited by Jorgebon on Sun Jan 06, 2008 12:36 pm, edited 1 time in total.
If you transfered all those patients from your other software before adding any current patients, then they can be found through the patient number. They should have numbers one to the total number of imported patients. If that's the case, then you only need to find the first patient you entered (maybe from the appointment book) and select all those with smaller patient numbers.