Query question

[premier1888]

Hi.

I took Dr. Bonilla's 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'

and modified it so that it filtered out bad accounts and inactive pt.s

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.BillingType = '40')
AND PatStatus = '0'
GROUP BY procedurelog.PatNum
HAVING MAX(procdate) < '2008-03-01'

However, the table that show procdate does not show date pt.'s last visit, as indicated in Dr. Bonilla's description, but rather their first visit. How do we make it so that it actually shows date of last visit?

[premier1888]

Ok, I looked back in the old posts, and found someone with a similar query, minus the filter for pt. account status, so I added that in.

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

I am not quite sure why my previous query gave the first visit date, and this one gives the last visit date, but it works. Now, how do I filter out the 12:00AM time from the last visit date so it doesn't appear in the mail merge?

[Jorgebon]

Sorry you had problems. I had fixed that query after it was posted. The query you need is as follows:

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'
AND patient.BillingType = '40'
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2008-01-01'
ORDER BY patient.address, patient.address2


Jorge Bonilla, DMD

[premier1888]

Great. Looks like I was on the right track. How do you get rid of the last visit time?

[Jorgebon]

To remove the time you can change it as follows:

SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.LName,patient.FName,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip, Date_Format(MAX(procedurelog.procdate),'%m/%d/%y') as DATE
FROM patient,procedurelog
WHERE procedurelog.patnum=patient.patnum
AND procedurelog.procstatus = '2'
AND patient.BillingType = '40'
GROUP BY procedurelog.patnum
HAVING MAX(procdate) < '2008-01-01'
ORDER BY patient.address, patient.address2


Jorge Bonilla, DMD

[premier1888]

Bingo! Thanks a bunch.
I don't understand it, but it works. I feel like a dog trying to learn calculus.

[Jorgebon]

I would also suggest you add a line to select only those patients that are not inactive, archived or deceased using the following as one of the conditions:

AND patient.PatStatus = '0'

Jorge Bonilla, DMD