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!
Query #287
Re: Query #287
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;
/*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;
Re: Query #287
Great I will try that! Thank you