Query #287

For users or potential users.
Post Reply
speeples
Posts: 27
Joined: Tue Feb 05, 2019 12:27 pm

Query #287

Post by speeples » Wed Oct 13, 2021 1:42 pm

I posted on the FB page about 2 weeks ago and have not gotten a response.....is there anyway to change it to Date of LAST Appointment?
Thank you!

joes
Posts: 239
Joined: Tue Aug 13, 2019 12:41 pm

Re: Query #287

Post by joes » Wed Oct 13, 2021 4:43 pm

You could add the address fields from query #287 to query #174. Set the BeforeDate at the top of the query, and it will get you the last visit date on or before that date. It uses the date of the last completed procedure as the DateLastVisit. If a patient does not have any completed procedures on or before the BeforeDate, they will not show in the list.

/*174 Last visit before given date for all active patients with phone numbers*/
SET @BeforeDate='2021-10-13';
SELECT p.PatNum,
Address,
Address2,
City,
State,
Zip
WkPhone,
HmPhone,
WirelessPhone AS CellPhone,
LastVisit.MaxProcDate AS DateLastVisit,
@BeforeDate AS DateBefore
FROM patient p
INNER JOIN (
SELECT pl.PatNum, MAX(pl.ProcDate) AS MaxProcDate
FROM procedurelog pl
WHERE pl.ProcStatus=2
GROUP BY pl.PatNum
HAVING MAX(pl.ProcDate)<=@BeforeDate
) LastVisit ON p.PatNum=LastVisit.PatNum
WHERE PatStatus=0
GROUP BY p.PatNum
ORDER BY p.LName,p.FName;
Joe Sullivan
Open Dental Software
http://www.opendental.com

speeples
Posts: 27
Joined: Tue Feb 05, 2019 12:27 pm

Re: Query #287

Post by speeples » Fri Oct 15, 2021 9:50 am

Great I will try that! Thank you

Post Reply