Query for Active patients with first visit before date

[spolevoy]

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]

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'

[spolevoy]

sorry, that comes out with no data

[allends]

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'