Free insurance fee check

Steps 1–2 · Copy and run
Copy the query. Run it in Open Dental.

Show the full query
/* =====================================================================================================================
   Praktend query, kit version 1.3

   WHAT IT DOES
   Adds up the insurance claim lines of the last 12 full months by insurance carrier and procedure code, and lists your
   standard fee for each procedure code. It only reads: it changes nothing in Open Dental. The result has totals and
   counts only. It has no patient names, birth dates, dates of service, addresses, phone numbers or ID numbers.
   Any carrier and procedure code with fewer than 11 claim lines is not listed.

   HOW TO RUN IT
   Open Dental: Reports > User Query (needs the Query permission). Paste this whole file, click Submit Query, click
   Export and save the file as .txt or .csv. Do not open it and save it again from Excel.
   Questions: chris@praktend.com
   ===================================================================================================================== */

SELECT
    x.row_type,
    x.period_from,
    x.period_to,
    x.proc_code,
    x.carrier_name,
    x.line_count,
    x.ucr_lines,
    x.contracted_lines,
    x.other_lines,
    ROUND(x.billed, 2)                 AS billed,
    ROUND(x.ins_paid, 2)               AS ins_paid,
    ROUND(x.pat_paid, 2)               AS pat_paid,
    ROUND(x.write_off, 2)              AS write_off,
    x.paid_in_full_count,
    ROUND(x.practice_fee, 2)           AS practice_fee,
    ROUND(x.plan_fee_amount, 2)        AS plan_fee_amount,
    ROUND(x.highest_full_fee, 2)       AS highest_full_fee,
    x.tier0_lines,
    x.tier0_corroborated_lines,
    ROUND(x.tier0_dollars, 2)          AS tier0_dollars,
    NULL                               AS tier0_dollars_confirmed,   /* left blank */
    ROUND(x.tier0_step_dollars, 2)     AS tier0_step_dollars,
    ROUND(x.writeoff_all_claims, 2)    AS writeoff_all_claims,
    x.ucr_schedule_count,
    x.fee_override_rows,
    x.suppression_note
FROM (

    /* ---------------------------------------------------------------------------------------------------------
       Part A: the detail rows, the carrier rows and the one total row.
       A carrier and procedure code with fewer than 11 claim lines is never listed. The carrier and total rows are
       built in two steps so that nothing can be subtracted from them to find such a group.
       The total row is always returned, even when there are no claim lines.
       --------------------------------------------------------------------------------------------------------- */
    SELECT
        CASE m.k WHEN 1 THEN 'detail' WHEN 2 THEN 'total' ELSE 'carrier' END        AS row_type,
        MAX(IF(m.k = 2, g.period_from, NULL))                                       AS period_from,
        MAX(IF(m.k = 2, g.period_to, NULL))                                         AS period_to,
        MAX(IF(m.k = 1, b.proc_code, NULL))                                         AS proc_code,
        MAX(IF(m.k IN (1, 3), b.carrier_name, NULL))                                AS carrier_name,
        IFNULL(SUM(IF(m.k = 1, b.line_count, b.line_count - IF(b.s_line_count < 11, b.s_line_count, 0))), 0) AS line_count,
        IFNULL(SUM(IF(m.k = 1, b.ucr_lines, b.ucr_lines - IF(b.s_line_count < 11, b.s_ucr_lines, 0))), 0)    AS ucr_lines,
        IFNULL(SUM(IF(m.k = 1, b.contracted_lines, b.contracted_lines - IF(b.s_line_count < 11, b.s_contracted_lines, 0))), 0) AS contracted_lines,
        IFNULL(SUM(IF(m.k = 1, b.other_lines, b.other_lines - IF(b.s_line_count < 11, b.s_other_lines, 0))), 0) AS other_lines,
        IFNULL(SUM(IF(m.k = 1, b.billed, b.billed - IF(b.s_line_count < 11, b.s_billed, 0))), 0)             AS billed,
        IFNULL(SUM(IF(m.k = 1, b.ins_paid, b.ins_paid - IF(b.s_line_count < 11, b.s_ins_paid, 0))), 0)       AS ins_paid,
        IF(m.k = 2, IFNULL(SUM(IF(m.k = 1, b.pat_paid, b.pat_paid - IF(b.s_line_count < 11, b.s_pat_paid, 0))), 0), NULL) AS pat_paid,
        IFNULL(SUM(IF(m.k = 1, b.write_off, b.write_off - IF(b.s_line_count < 11, b.s_write_off, 0))), 0)    AS write_off,
        IFNULL(SUM(IF(m.k = 1, b.paid_in_full_count, b.paid_in_full_count - IF(b.s_line_count < 11, b.s_paid_in_full_count, 0))), 0) AS paid_in_full_count,
        MAX(IF(m.k = 1, b.practice_fee, NULL))                                      AS practice_fee,
        MAX(IF(m.k = 1, b.plan_fee_amount, NULL))                                   AS plan_fee_amount,
        MAX(IF(m.k = 1, b.highest_full_fee, NULL))                                  AS highest_full_fee,
        IFNULL(SUM(IF(m.k = 1, b.tier0_lines, b.tier0_lines - IF(b.s_line_count < 11, b.s_tier0_lines, 0))), 0) AS tier0_lines,
        IFNULL(SUM(IF(m.k = 1, b.tier0_corroborated_lines, b.tier0_corroborated_lines - IF(b.s_line_count < 11, b.s_tier0_corroborated_lines, 0))), 0) AS tier0_corroborated_lines,
        IFNULL(SUM(IF(m.k = 1, b.tier0_dollars, b.tier0_dollars - IF(b.s_line_count < 11, b.s_tier0_dollars, 0))), 0) AS tier0_dollars,
        IFNULL(SUM(IF(m.k = 1, b.tier0_step_dollars, b.tier0_step_dollars - IF(b.s_line_count < 11, b.s_tier0_step_dollars, 0))), 0) AS tier0_step_dollars,
        MAX(IF(m.k = 2, g.writeoff_all_claims, NULL))                               AS writeoff_all_claims,
        MAX(IF(m.k = 2, g.ucr_schedule_count, NULL))                                AS ucr_schedule_count,
        MAX(IF(m.k = 2, g.fee_override_rows, NULL))                                 AS fee_override_rows,
        /* which privacy rule applied to this row */
        CASE m.k WHEN 1 THEN 'groups under 11 lines are not returned'
                 WHEN 2 THEN 'sums cover the itemized groups of every carrier; the groups under 11 lines of a carrier are included only when that carrier has 11 or more lines in them'
                 ELSE 'sums cover the itemized groups of this carrier; its groups under 11 lines are included only when they total 11 or more lines' END                                                   AS suppression_note
    FROM (SELECT 1 AS k UNION ALL SELECT 2 UNION ALL SELECT 3) m
    CROSS JOIN (
        /* g: facts about the whole practice, for the total row. Its date window must match the one inside `raw` below. */
        SELECT
            DATE_FORMAT(w.win_from, '%Y-%m')                                        AS period_from,
            DATE_FORMAT(DATE_SUB(w.win_to_excl, INTERVAL 1 DAY), '%Y-%m')           AS period_to,
            (SELECT SUM(IFNULL(c2.WriteOff, 0)) FROM claimproc c2
              WHERE c2.DateCP >= w.win_from AND c2.DateCP < w.win_to_excl)          AS writeoff_all_claims,
            (SELECT COUNT(DISTINCT pr.FeeSched) FROM provider pr
              WHERE pr.IsHidden = 0 AND pr.FeeSched > 0)                            AS ucr_schedule_count,
            (SELECT COUNT(*) FROM fee fo
              WHERE fo.FeeSched = w.ucr_sched AND (fo.ClinicNum <> 0 OR fo.ProvNum <> 0))
                                                                                    AS fee_override_rows
        FROM (
            SELECT
                CAST(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m-01') AS DATE) AS win_from,
                CAST(DATE_FORMAT(CURDATE(), '%Y-%m-01') AS DATE)                             AS win_to_excl,
                (SELECT pr.FeeSched FROM provider pr
                  WHERE pr.IsHidden = 0 AND pr.FeeSched > 0
                  GROUP BY pr.FeeSched ORDER BY COUNT(*) DESC, pr.FeeSched ASC LIMIT 1)      AS ucr_sched
        ) w

    ) g
    LEFT JOIN (

        /* -----------------------------------------------------------------------------------------------------
           b: step 1. Groups of 11 or more claim lines pass through as detail rows. Each carrier also gets one summary
           row with its sums over all its groups and, in the s_ columns, over its groups under 11 lines.
           ----------------------------------------------------------------------------------------------------- */
        SELECT
            IF(m2.k = 1, 'detail', 'cs')                                            AS rt,
            MAX(IF(m2.k = 1, p.proc_code, NULL))                                    AS proc_code,
            p.carrier_name                                                          AS carrier_name,
            SUM(p.line_count)                               AS line_count,
            SUM(p.ucr_lines)                                AS ucr_lines,
            SUM(p.contracted_lines)                         AS contracted_lines,
            SUM(p.other_lines)                              AS other_lines,
            SUM(p.billed)                                   AS billed,
            SUM(p.ins_paid)                                 AS ins_paid,
            SUM(p.pat_paid)                                 AS pat_paid,
            SUM(p.write_off)                                AS write_off,
            SUM(p.paid_in_full_count)                       AS paid_in_full_count,
            SUM(p.tier0_lines)                              AS tier0_lines,
            SUM(p.tier0_corroborated_lines)                 AS tier0_corroborated_lines,
            SUM(p.tier0_dollars)                            AS tier0_dollars,
            SUM(p.tier0_step_dollars)                       AS tier0_step_dollars,
            MAX(IF(m2.k = 1, p.practice_fee, NULL))                                 AS practice_fee,
            MAX(IF(m2.k = 1, p.plan_fee_amount, NULL))                              AS plan_fee_amount,
            MAX(IF(m2.k = 1, p.highest_full_fee, NULL))                             AS highest_full_fee,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.line_count, 0)), NULL)                   AS s_line_count,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.ucr_lines, 0)), NULL)                    AS s_ucr_lines,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.contracted_lines, 0)), NULL)             AS s_contracted_lines,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.other_lines, 0)), NULL)                  AS s_other_lines,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.billed, 0)), NULL)                       AS s_billed,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.ins_paid, 0)), NULL)                     AS s_ins_paid,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.pat_paid, 0)), NULL)                     AS s_pat_paid,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.write_off, 0)), NULL)                    AS s_write_off,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.paid_in_full_count, 0)), NULL)           AS s_paid_in_full_count,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.tier0_lines, 0)), NULL)                  AS s_tier0_lines,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.tier0_corroborated_lines, 0)), NULL)     AS s_tier0_corroborated_lines,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.tier0_dollars, 0)), NULL)                AS s_tier0_dollars,
            IF(m2.k = 2, SUM(IF(p.line_count < 11, p.tier0_step_dollars, 0)), NULL)           AS s_tier0_step_dollars
        FROM (

            /* -----------------------------------------------------------------------------------------------------
               p: one row per procedure code and carrier, added up from the claim lines (`lines` below).
               ----------------------------------------------------------------------------------------------------- */
            SELECT
                l.proc_code,
                l.carrier_name,
                COUNT(*)                                                                AS line_count,
                SUM(l.scope = 'ucr')                                                    AS ucr_lines,
                SUM(l.scope = 'contracted')                                             AS contracted_lines,
                SUM(l.scope = 'other')                                                  AS other_lines,
                SUM(l.fee_billed)                                                       AS billed,
                SUM(l.ins_pay)                                                          AS ins_paid,
                SUM(l.pat_paid)                                                         AS pat_paid,
                SUM(l.wo)                                                               AS write_off,
                SUM(l.is_full)                                                          AS paid_in_full_count,
                MAX(l.ucr_fee)                                                          AS practice_fee,
                MAX(IF(l.scope = 'contracted', l.plan_amt, NULL))                       AS plan_fee_amount,
                MAX(IF(l.is_full = 1, l.fee_billed, NULL))                              AS highest_full_fee,
                SUM(l.t0_gap > 0)                                                       AS tier0_lines,
                /* corroborated: the carrier paid this contracted line on the full billed fee with nothing written off */
                SUM(IF(l.t0_gap > 0 AND ABS(l.wo) < 0.005 AND l.fee_billed >= l.ucr_fee - 0.005
                            AND l.ins_pay > 0 AND l.ins_pay >= l.ins_est - 1, 1, 0))    AS tier0_corroborated_lines,
                SUM(l.t0_gap)                                                           AS tier0_dollars,
                SUM(IF(l.t0_gap > 0, LEAST(l.t0_gap, CEIL(ROUND(l.ucr_fee * 1.10, 2)) - l.ucr_fee), 0))
                                                                                        AS tier0_step_dollars
            FROM (

                /* ---------------------------------------------------------------------------------------------
                   lines: adds the two per-line tests to each claim line.
                   ---------------------------------------------------------------------------------------------- */
                SELECT
                    r.*,
                    /* paid-in-full test. A missing value anywhere means no. */
                    IF(r.scope = 'ucr' AND r.fee_billed > 0 AND r.pct > 0 AND r.ins_pay > 0
                       AND ABS(r.wo) < 0.005
                       AND (r.ins_pay / (NULLIF(r.pct, 0) / 100) + GREATEST(r.ded, 0)) >= 0.98 * r.fee_billed
                       AND r.ins_pay >= r.ins_est - 1
                       AND r.proc_code NOT REGEXP '^D(239[1-4]|27[0-9][0-9]|295[0-4])$', 1, 0)    AS is_full,
                    /* fee-gap test: a contracted plan whose fee for the code is above the practice fee */
                    IF(r.scope = 'contracted' AND r.plan_amt IS NOT NULL AND r.ucr_fee IS NOT NULL
                       AND r.plan_amt > r.ucr_fee + 0.005, r.plan_amt - r.ucr_fee, 0)             AS t0_gap
                FROM (

                    /* -----------------------------------------------------------------------------------------
                       raw: one row per claim line (procedure + plan), straight from Open Dental's tables.
                       This is the only block that touches patient-level tables. Its rows never leave the query.
                       ----------------------------------------------------------------------------------------- */
                    SELECT
                        IF(pc.ProcCode REGEXP '^D[0-9]{4}$', UPPER(pc.ProcCode), 'OTHER')            AS proc_code,
                        COALESCE(NULLIF(TRIM(ca.CarrierName), ''), '(unnamed carrier)')              AS carrier_name,
                        CASE
                            WHEN ip.PlanNum IS NULL THEN 'other'
                            WHEN (ip.FeeSched = 0 OR us.fs IS NOT NULL)
                                 AND LOWER(TRIM(IFNULL(ip.PlanType, ''))) IN ('', 'p') THEN 'ucr'
                            WHEN ip.FeeSched > 0 AND us.fs IS NULL
                                 AND (ip.ClaimsUseUCR = 1 OR LOWER(TRIM(IFNULL(ip.PlanType, ''))) = 'p') THEN 'contracted'
                            ELSE 'other'
                        END                                                                          AS scope,
                        IF(pc.ProcCode REGEXP '^D[0-9]{4}$', uf.amt, NULL)                           AS ucr_fee,
                        pf.amt                                                                       AS plan_amt,
                        IFNULL(ps.amt, 0)                                                            AS pat_paid,
                        SUM(IF(cp.Status = 1, IFNULL(cp.FeeBilled, 0), 0))                           AS fee_billed,
                        SUM(IFNULL(cp.InsPayAmt, 0))                                                 AS ins_pay,
                        SUM(IF(cp.Status = 1, IFNULL(cp.InsPayEst, 0), 0))                           AS ins_est,
                        SUM(IF(cp.Status = 1, IFNULL(cp.DedApplied, 0), 0))                          AS ded,
                        SUM(IFNULL(cp.WriteOff, 0))                                                  AS wo,
                        /* benefit percent on the original payment: the override if set, else the plan percent; -1 = blank */
                        MAX(IF(cp.Status = 1,
                               IF(IFNULL(cp.PercentOverride, -1) >= 0, cp.PercentOverride, IFNULL(cp.Percentage, -1)),
                               NULL))                                                                AS pct
                    FROM claimproc cp
                    /* primary claims only */
                    JOIN claim c           ON c.ClaimNum = cp.ClaimNum AND c.ClaimType = 'P'
                    /* completed procedures only */
                    JOIN procedurelog pl   ON pl.ProcNum = cp.ProcNum AND pl.ProcStatus = 2
                    JOIN procedurecode pc  ON pc.CodeNum = pl.CodeNum
                    LEFT JOIN insplan ip   ON ip.PlanNum = cp.PlanNum
                    LEFT JOIN carrier ca   ON ca.CarrierNum = ip.CarrierNum
                    /* us: the fee schedules the practice's own providers use (a plan on one of these is not contracted) */
                    LEFT JOIN (SELECT DISTINCT FeeSched AS fs FROM provider WHERE FeeSched > 0) us ON us.fs = ip.FeeSched
                    /* u: the 12 full calendar months before this month, and the practice's own (UCR) fee schedule number */
                    CROSS JOIN (
                        SELECT
                            CAST(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m-01') AS DATE) AS win_from,
                            CAST(DATE_FORMAT(CURDATE(), '%Y-%m-01') AS DATE)                             AS win_to_excl,
                            (SELECT pr.FeeSched FROM provider pr
                              WHERE pr.IsHidden = 0 AND pr.FeeSched > 0
                              GROUP BY pr.FeeSched ORDER BY COUNT(*) DESC, pr.FeeSched ASC LIMIT 1)      AS ucr_sched
                    ) u
                    /* uf: today's practice fee for the procedure's code; pf: the plan's schedule amount for it. Global rows only. */
                    LEFT JOIN (SELECT FeeSched, CodeNum, MAX(Amount) AS amt FROM fee
                                WHERE ClinicNum = 0 AND ProvNum = 0 AND Amount > 0
                                GROUP BY FeeSched, CodeNum) uf ON uf.FeeSched = u.ucr_sched AND uf.CodeNum = pl.CodeNum
                    LEFT JOIN (SELECT FeeSched, CodeNum, MAX(Amount) AS amt FROM fee
                                WHERE ClinicNum = 0 AND ProvNum = 0 AND Amount > 0
                                GROUP BY FeeSched, CodeNum) pf ON pf.FeeSched = ip.FeeSched AND pf.CodeNum = pl.CodeNum
                    /* ps: patient payments applied to each procedure (a total per procedure; no patient is selected) */
                    LEFT JOIN (SELECT ProcNum, SUM(SplitAmt) AS amt FROM paysplit
                                WHERE ProcNum > 0 AND IFNULL(IsDiscount, 0) = 0
                                GROUP BY ProcNum) ps ON ps.ProcNum = cp.ProcNum
                    WHERE cp.Status IN (1, 4)                       /* received or supplemental */
                      AND IFNULL(cp.IsTransfer, 0) = 0
                      AND cp.DateCP >= u.win_from AND cp.DateCP < u.win_to_excl
                    GROUP BY cp.ProcNum, cp.PlanNum, pc.ProcCode, ip.PlanNum, ip.PlanType, ip.FeeSched, ip.ClaimsUseUCR,
                             ca.CarrierName, us.fs, uf.amt, pf.amt, ps.amt
                    HAVING SUM(cp.Status = 1) > 0                                           /* an original payment in the window */
                       AND MAX(IFNULL(cp.PaidOtherIns, 0) > 0) = 0                          /* nothing paid first by other insurance */
                       AND MAX(IFNULL(cp.NoBillIns, 0) = 1) = 0                             /* not marked do-not-bill */
                       AND MIN(UPPER(TRIM(IFNULL(cp.CodeSent, ''))) = UPPER(TRIM(pc.ProcCode))) = 1   /* same code sent */

                ) r

            ) l
            GROUP BY l.proc_code, l.carrier_name

            ) p
        CROSS JOIN (SELECT 1 AS k UNION ALL SELECT 2) m2
        /* Privacy rule (1 of 2): only groups of 11 or more claim lines are listed. Do not change the number 11. */
        WHERE m2.k = 2 OR p.line_count >= 11
        GROUP BY m2.k, IF(m2.k = 1, p.proc_code, ''), p.carrier_name

    ) b ON 1 = 1
    WHERE (m.k = 1 AND b.rt = 'detail') OR (m.k <> 1 AND b.rt = 'cs') OR (b.rt IS NULL AND m.k = 2)
    GROUP BY m.k, IF(m.k = 1, b.proc_code, ''), IF(m.k = 2, '', b.carrier_name)
    /* Privacy rule (2 of 2): a carrier row is returned only when it covers 11 or more claim lines. The total row is always
       returned. Do not change the number 11. */
    HAVING m.k <> 3 OR SUM(b.line_count - IF(b.s_line_count < 11, b.s_line_count, 0)) >= 11

    UNION ALL

    /* ---------------------------------------------------------------------------------------------------------
       Part B: one fee row per standard dental procedure code (D plus four digits) from the practice's default (UCR)
       fee schedule: the global row (clinic 0, provider 0). Code and dollar amount only.
       --------------------------------------------------------------------------------------------------------- */
    SELECT
        'fee', NULL, NULL, UPPER(pc.ProcCode), NULL,
        NULL, NULL, NULL, NULL,                       /* line counts */
        NULL, NULL, NULL, NULL, NULL,                 /* billed, ins_paid, pat_paid, write_off, paid_in_full_count */
        fe.Amount, NULL, NULL,                        /* practice_fee, plan_fee_amount, highest_full_fee */
        NULL, NULL, NULL, NULL,                       /* fee-gap columns */
        NULL, NULL, NULL,                             /* writeoff_all_claims, ucr_schedule_count, fee_override_rows */
        NULL                                          /* suppression_note */
    FROM procedurecode pc
    JOIN (SELECT FeeSched, CodeNum, MAX(Amount) AS Amount FROM fee
           WHERE ClinicNum = 0 AND ProvNum = 0 AND Amount > 0
           GROUP BY FeeSched, CodeNum) fe ON fe.CodeNum = pc.CodeNum
    JOIN (SELECT pr.FeeSched FROM provider pr
           WHERE pr.IsHidden = 0 AND pr.FeeSched > 0
           GROUP BY pr.FeeSched ORDER BY COUNT(*) DESC, pr.FeeSched ASC LIMIT 1) us1 ON us1.FeeSched = fe.FeeSched
    WHERE pc.ProcCode REGEXP '^D[0-9]{4}$'

) x
ORDER BY FIELD(x.row_type, 'total', 'carrier', 'detail', 'fee'), x.carrier_name, x.proc_code
Copy the query above.
In Open Dental, open Reports > User Query, paste it, click Submit Query, then Export. You need the Query permission. Save as .txt or .csv. Don't re-save it in Excel.

No permission?

Ask your Open Dental admin, or email us for a call.
Upload the file below. Then tick your in-network carriers.
Step 3 · Send the file
Upload your file.

Sending a file from this page needs JavaScript. Email chris@praktend.com and we help you send it.
We're not taking new fee checks through this page right now.
Book a 15-minute demo
Questions

Do we sign anything?
No. The fee check needs no signed agreement.
Does this connect to Open Dental?
No. You run the query, and it changes nothing.
Prefer to do it on a call?
Email chris@praktend.com and we'll set up a call.
Which carriers pay your full fee?

Copy the query

Run it in Open Dental

Upload the file

Copy the query. Run it in Open Dental.

Upload your file.

Thank you.

Questions

Which carriers pay your full fee?

How it works

Copy the query

Run it in Open Dental

Upload the file

Copy the query. Run it in Open Dental.

Upload your file.

Thank you.

Questions
