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'

[ishubham2101]

Late, but this one ended without an answer and the reason the second query returned nothing is worth writing down, because it is a mistake that is very easy to make and gives no error.

The second query groups by two columns:

Code: Select all

GROUP BY procedurelog.patnum, procedurelog.procdate
HAVING MAX(procdate) > '2019-01-01' AND MIN(procdate) < '2002-01-31'

Grouping by patnum and procdate makes every group a single procedure. Inside a group of one row, MAX(procdate) and MIN(procdate) are both that row's own date, so the HAVING is asking for one date that is after 2019 and before 2002. Nothing can satisfy that, so it returns zero rows on any database. It is not spolevoy's data. The third query drops procdate from the GROUP BY and is the right shape.

The original query has a different problem, and it is the one that made the data look wrong rather than empty. There is no ProcStatus filter, so treatment planned and deleted procedures are counted alongside completed ones, and Open Dental stores an unset date as 0001-01-01. MIN(ProcDate) over a patient who has any procedure carrying that sentinel returns 0001-01-01, which is less than 2002-01-31, so that patient passes the "first visit before 2002" test no matter when they actually first came in. On a chart of any age that is a lot of false positives.

One more thing specific to a takeover. patient.DateFirstVisit is an editable field, and on a converted or taken over chart it is very often blank or set to the conversion date for every patient at once. So for a 20 year anniversary list it is the wrong column to trust on its own. Worth comparing it against the earliest completed procedure before you believe either.

This is what I use. It reports both, and flags the patients where the two disagree by more than a month so you can see how much of the chart has a usable DateFirstVisit at all:

Code: Select all

SELECT
    pat.PatNum,
    CONCAT(pat.LName, ', ', pat.FName)      AS patient,
    pat.Birthdate,
    CASE WHEN YEAR(pat.DateFirstVisit) < 1880 THEN ''
         ELSE CAST(pat.DateFirstVisit AS CHAR) END AS first_visit_field,
    first_proc.first_completed              AS first_completed_procedure,
    CASE WHEN YEAR(pat.DateFirstVisit) < 1880 THEN 'field never set'
         WHEN first_proc.first_completed IS NULL THEN 'no completed procedures'
         WHEN ABS(DATEDIFF(pat.DateFirstVisit, first_proc.first_completed)) > 31
              THEN 'field and procedures disagree'
         ELSE '' END                        AS check_this,
    TIMESTAMPDIFF(YEAR, first_proc.first_completed, CURDATE()) AS years_with_practice,
    last_proc.last_completed                AS last_completed_procedure,
    COALESCE(NULLIF(prov.Abbr, ''), '')     AS primary_provider
FROM patient pat
    LEFT JOIN (
        SELECT pl.PatNum, MIN(pl.ProcDate) AS first_completed
        FROM procedurelog pl
        WHERE pl.ProcStatus = 2 AND YEAR(pl.ProcDate) > 1880
        GROUP BY pl.PatNum
    ) first_proc ON first_proc.PatNum = pat.PatNum
    LEFT JOIN (
        SELECT pl.PatNum, MAX(pl.ProcDate) AS last_completed
        FROM procedurelog pl
        WHERE pl.ProcStatus = 2 AND YEAR(pl.ProcDate) > 1880
        GROUP BY pl.PatNum
    ) last_proc ON last_proc.PatNum = pat.PatNum
    LEFT JOIN provider prov ON prov.ProvNum = pat.PriProv
WHERE pat.PatStatus = 0
  AND COALESCE(first_proc.first_completed, pat.DateFirstVisit) < '2020-01-01'   -- <-- edit this
  AND YEAR(COALESCE(first_proc.first_completed, pat.DateFirstVisit)) > 1880
ORDER BY COALESCE(first_proc.first_completed, pat.DateFirstVisit);

Set the date on the line marked edit this. ProcStatus = 2 is completed. The YEAR(x) > 1880 tests are what keep the 0001-01-01 sentinel out of the MIN, which is the part the original was missing.

Run on 26.2. It is in a public repo with the others I have written, all run against the schema by CI before they ship: https://github.com/ishubham21/opendental-queries