"Not consistently, and only for some patients" is the tell. That pattern almost always means the notes imported fine but are attached to a
duplicate plan record. An import typically creates a second insplan row for the same carrier + group. The note lands on one copy, and some patients' inssub still points at the other copy, which has an empty note. In the UI the note just looks missing for those patients.
Two read-only queries to confirm it before you change anything.
1. Carrier/group combinations that have both a note-bearing and a note-less copy:
Code: Select all
SELECT
c.CarrierName,
ip.GroupNum,
ip.GroupName,
COUNT(DISTINCT ip.PlanNum) AS plan_copies,
SUM(CASE WHEN TRIM(COALESCE(ip.PlanNote,'')) = '' THEN 1 ELSE 0 END) AS copies_without_note,
SUM(CASE WHEN TRIM(COALESCE(ip.PlanNote,'')) <> '' THEN 1 ELSE 0 END) AS copies_with_note,
COUNT(DISTINCT pp.PatNum) AS patients_attached
FROM insplan ip
INNER JOIN carrier c ON c.CarrierNum = ip.CarrierNum
LEFT JOIN inssub s ON s.PlanNum = ip.PlanNum
LEFT JOIN patplan pp ON pp.InsSubNum = s.InsSubNum
GROUP BY c.CarrierName, ip.GroupNum, ip.GroupName
HAVING copies_without_note > 0 AND copies_with_note > 0
ORDER BY patients_attached DESC, plan_copies DESC;
Any row returned is a group where the note exists on one copy and not another.
2. The specific patients sitting on the note-less copy, with their clinic:
Code: Select all
SELECT
c.CarrierName,
ip.GroupNum,
ip.GroupName,
ip.PlanNum AS plan_without_note,
twin.PlanNum AS plan_with_note,
pat.ClinicNum,
pat.PatNum, pat.LName, pat.FName
FROM insplan ip
INNER JOIN carrier c ON c.CarrierNum = ip.CarrierNum
INNER JOIN insplan twin
ON twin.CarrierNum = ip.CarrierNum
AND twin.GroupNum = ip.GroupNum
AND twin.PlanNum <> ip.PlanNum
AND TRIM(COALESCE(twin.PlanNote,'')) <> ''
INNER JOIN inssub s ON s.PlanNum = ip.PlanNum
INNER JOIN patplan pp ON pp.InsSubNum = s.InsSubNum
INNER JOIN patient pat ON pat.PatNum = pp.PatNum
WHERE TRIM(COALESCE(ip.PlanNote,'')) = ''
ORDER BY c.CarrierName, ip.GroupNum, pat.LName;
plan_with_note is where the text you are missing actually lives. If the affected patients cluster in one ClinicNum, that confirms it came in with that clinic's import.
Two things worth checking before you conclude anything:
- You may be looking at the wrong note field. insplan.PlanNote is the plan-level note. There is also a subscriber-level note on inssub — if what's missing is per-subscriber rather than per-plan, swap ip.PlanNote for the inssub note column and re-run.
- Run both read-only first and confirm against two or three patients in the UI before merging or repointing anything. Duplicate-plan cleanup is easy to get wrong and hard to undo. Column names shift slightly between versions, so if either query errors, post your version and the error and I'll adjust it.
If the queries come back empty then it isn't duplicates, and I'd look at the import path instead — say so and I'll suggest what to check next.
I write custom Open Dental queries to order if that's easier than doing it yourself: $150 for one query in 48 hours, $450 for three plus a Google Sheet that refreshes. Delivered as a .sql file you run on your own server, so no patient data leaves your building and there's nothing to sign.