question on example query

[drtbar]

In the query:

39. List of patients and their addresses who have not been seen since a certain date (the example uses Sept 1, 2005). It will also give you the date of their last visit. Submitted by Jorge Bonilla, DMD
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) < '2005-09-01'

What does

AND procedurelog.procstatus = '2'

mean, or what does it return?

[Jorgebon]

Procstatus = '2' means procedures that have been completed. this limits the output of the query to only patients that had previously been seen in the office because only patients who have actually been to the office have completed procedures.
I recommend you try a more refined version of this query:

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) < '2006-01-01'
ORDER BY patient.address, patient.address2

Jorge Bonilla, DMD

[drtbar]

Well what I'm trying to get is last name, first name, address, city, state, zip, birthdate, homephone, and last visit which I was able to get with:

SELECT patient.LName, patient.FName, patient.Address, patient.City, patient.State, patient.Zip, patient.Birthdate, patient.hmphone, procedurelog.procdate
FROM patient,procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.procstatus = '2'
GROUP BY patient.lname

but it doesn't give me all of my patients. I just had my data converted from my old pms (dentech) so I don't think most of my patients have been seen yet as far as opendent is concerned. I'm able to get the data I want, I just want it for every patient in the database.

Thanks in advance

[Mifa]

Maybe you should add another condition in the WHERE section, to check if the patient is still active:

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 patient.patstatus = 0
AND procedurelog.procstatus = '2'
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2006-01-01'
ORDER BY patient.address, patient.address2

just a suggestion...

[Jorgebon]

That's a very good idea, Mifa.
DTbar, If you had the database information imported from a previous practice software, then this query might not be the one you want. In any case, you can delete the line that refers to the completed procedures and see what you get. Since I don't know exactly what information you were able to salvage from the previous software, it would be more of a trial and error process for me to arrive at the query that would work for you. In any case this query relies on procedures performed on patients to determine when they came to the office, so if you didn't get that information transferred to OD, then I don't think this query will work for you.
Jorge Bonilla. DMD

[drtbar]

I'm obviously new to the software and even newer to queries

I bought the SQL for dummies book and have been reading it here and there, but I stumbled on your example and tried to modify it to make it fit my needs. Will play around some more and see what I can come up with. Thanks for your help.