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?
Query for Active patients with first visit before date
Re: Query for Active patients with first visit before date
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'
Re: Query for Active patients with first visit before date
sorry, that comes out with no data
Re: Query for Active patients with first visit before date
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):
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'