Query for Active patients with first visit before date

For users or potential users.
Post Reply
spolevoy
Posts: 74
Joined: Wed Oct 17, 2012 4:45 am

Query for Active patients with first visit before date

Post by spolevoy » Thu Dec 30, 2021 6:11 am

I have a 20 year anniversary of my practice takeover coming up, would like to know who are the patients that have stayed with me since then.
This query from Patient Raw does not return correct data

SELECT patient.LName,patient.FName,patient.DateFirstVisit FROM patient,procedurelog WHERE procedurelog.patnum=patient.patnum AND (patient.PriProv = '1') GROUP BY procedurelog.patnum HAVING MAX(procdate) > '2019-01-01' AND MIN(procdate) < '2002-01-31'

Any ideas?

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Query for Active patients with first visit before date

Post by allends » Thu Dec 30, 2021 9:39 am

Is this more accurate? I added another column for most recent date and removed the procedures with MinVal dates.
SELECT patient.LName,patient.FName,patient.DateFirstVisit,MAX(procedurelog.ProcDate) DateMostRecent FROM patient INNER JOIN procedurelog ON patient.patnum=procedurelog.PatNum WHERE patient.DateFirstVisit != '0001-01-01' GROUP BY procedurelog.patnum,procedurelog.procdate HAVING MAX(procdate) > '2019-01-01' AND MIN(procdate) < '2002-01-31'
Allen
Open Dental Software
http://www.opendental.com

spolevoy
Posts: 74
Joined: Wed Oct 17, 2012 4:45 am

Re: Query for Active patients with first visit before date

Post by spolevoy » Thu Dec 30, 2021 11:16 am

sorry, that comes out with no data

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Query for Active patients with first visit before date

Post by allends » Fri Dec 31, 2021 9:13 am

I recommend calling Open Dental for help then. They will be able to walk through the query with you for the context of your database.

You could also try this query (If it is still empty, you may want to adjust your min date):
SELECT patient.LName,patient.FName,MIN(procedurelog.ProcDate) DateFirstVisit,MAX(procedurelog.ProcDate) DateMostRecent FROM patient INNER JOIN procedurelog ON patient.patnum=procedurelog.PatNum WHERE procedurelog.ProcDate != '0001-01-01' GROUP BY procedurelog.patnum HAVING MAX(procdate) > '2019-01-01' AND MIN(procdate) < '2002-01-31'
Allen
Open Dental Software
http://www.opendental.com

Post Reply