Custom Open Dental queries in 48 hours - $150, delivered as a .sql file you run yourself

[ishubham2101]

Open Dental's own documentation says custom query requests are backlogged and "may take several weeks to process and write," and it suggests hiring a third-party developer. I would like to be that option for anyone who does not want to wait.

How it works: you describe the report you need, I write and test the SQL, and you get a .sql file that you paste into Reports > User Query and run yourself.

Why it is delivered that way: I never remote into anything, and no patient data ever leaves your office. There is no BAA to negotiate, because I never touch your database. You run it, you see the output.

Price: $150 for one query, delivered within 48 hours. $450 for three. If the query does not return what you asked for, you do not pay. I would rather fix it or walk away than argue about it.

About me: I am a senior software engineer - JavaScript and TypeScript by day, SQL for several years. I am new to this forum and I am not going to pretend I have years in the Open Dental schema specifically. The schema is published and well documented, and read-only reporting SQL is squarely what I do for a living. That is also exactly why the guarantee exists: you are not the one taking the risk on my learning curve.

Payment through Upwork escrow if you would like the protection of it, or a plain invoice. Whichever you are comfortable with.

If you would like to try it, reply here describing what you are trying to get out of the database. Please do not post any patient information - just describe the report. For example, "production by provider by month, excluding adjustments," or "patients with treatment planned procedures over $500 that have not been scheduled in the last 90 days."

I will answer the first request in this thread publicly and at no charge, so you can judge the quality before anyone pays me anything.

[ishubham2101]

Rather than let this sit as an advert, here is a working one, free, so you can judge the quality before anyone pays me anything.

This is the report most practices ask for first: treatment you have already diagnosed that nobody has booked. It is money that is already sitting in your database.

Code: Select all

/* Unscheduled treatment planned work, grouped by patient.
   Read only. Run from Reports > User Query.
   "Unscheduled" here means: treatment planned, not attached to an
   appointment, and not sitting on a planned appointment either. */
SELECT
    pat.PatNum,
    CONCAT(pat.LName, ', ', pat.FName)   AS Patient,
    pat.WirelessPhone,
    pat.HmPhone,
    COUNT(pl.ProcNum)                    AS ProcCount,
    ROUND(SUM(pl.ProcFee), 2)            AS TxPlannedFees,
    MAX(pl.DateTP)                       AS LastPlannedOn,
    (SELECT MAX(p2.ProcDate)
       FROM procedurelog p2
      WHERE p2.PatNum = pat.PatNum
        AND p2.ProcStatus = 2)           AS LastCompletedProc
FROM procedurelog pl
INNER JOIN patient pat ON pat.PatNum = pl.PatNum
WHERE pl.ProcStatus    = 1   /* 1 = treatment planned */
  AND pl.AptNum        = 0   /* not attached to an appointment */
  AND pl.PlannedAptNum = 0   /* not on a planned appointment */
  AND pl.ProcFee       > 0
  AND pat.PatStatus    = 0   /* 0 = active patient */
GROUP BY pat.PatNum, pat.LName, pat.FName, pat.WirelessPhone, pat.HmPhone
HAVING SUM(pl.ProcFee) >= 500
ORDER BY TxPlannedFees DESC;

Notes, because the caveats matter more than the SQL:

- Change the 500 in the HAVING line to whatever floor you care about. Set it to 0 to see everything.
- Procedures marked TPi (inactive treatment plan, ProcStatus are excluded on purpose. If you want them, change the ProcStatus filter to IN (1,8).
- LastCompletedProc is there so you can tell a patient who was in last month from one you have not seen in three years. They are very different phone calls.
- DateTP can be 0001-01-01 on older rows that were migrated, so do not be alarmed by odd dates in LastPlannedOn.
- Run it read only first and sanity check the total against what you would expect. If your version names a column differently, tell me which and I will adjust it.

If the number that comes back is large, that is normal, and it is the point. The useful follow up is usually the same report split by provider, or filtered to patients with active insurance benefits remaining this year.

Happy to write a custom one against your specific question on the terms in the first post ($150, 48 hours, no charge if it does not do what you asked). But the query above is yours either way.