Custom Query for Discount Plan

[ssebree]

Our office is trying to dig into the financial details of our in-house membership/discount plan to better understand the profitability of it and if we should increase our discount plan membership fee and/or adjust the current discounts we're offering. Specifically, I want to run a report for a date range showing all patients enrolled in the discount plan with procedures, production, write-offs, adjustments, and collections/income. How can I create this report myself or hire someone else to do it? If hiring a third-party query writer, how do I go about finding someone to create this custom query for me? I've been searching Google but having no luck.

[allends]

ssebree,
I removed the duplicate posts in the other forums to keep answers focused in a single thread.
Open Dental has a team dedicated to this, but we are not currently taking query requests. This may change in the future as staffing improves.
Check this link later and it may be open for new requests: https://www.opendental.com/manual221/queryrequest.html

[ssebree]

Hi adearmondsattler, thanks for updating me on this. I understand being short staffed and the challenges that come with that. I think we're all experiencing the squeeze of the current employment climate. However, with Open Dental not offering any other solutions to their customers to generate these reports it feels a bit unfair to not try to help us out by providing a list of third-party query writers who we can work with temporarily. Or provide useful tools and resources to teach me how to create my own queries. Can you provide anything like this? I'd love to learn how to create them myself or I'd be happy to pay a third-party to create the reports for me.

[allends]

Ssebree,
There are a large number of online resources for learning SQL. Open Dental does not endorse any particular one, but Google is a great resource for finding them.
Here are a couple helpful links to put what you learn about SQL into practice with Open Dental's schema:
https://www.opendental.com/site/program ... urces.html
https://www.opendental.com/OpenDentalDo ... on22-1.xml

We do not have a list of 3rd party query writers that we endorse. A place to look for assistance on queries in general would be our official Facebook user group: https://www.facebook.com/groups/opendentalusersgroup/. The users there may be able to assist you with what you are looking for.

[ssebree]

Thank you, Allen! I'll dig into those links and see what I can figure out. I appreciate your help and prompt responses!

[Tom Zaccaria]

Does this help?
Change the date in line 1 and change whatever you call your discount plan near the end, third last line. We use' Value Plan'.
drtmz

/*264 List of procedures for a date range and specified carrier showing patient name, service date, procedure code, primary insurance carrier, fee, sum of ins est, sum ins paid*/

SET @FromDate='2021-01-01' , @ToDate='2021-12-31';

/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 05/06/2009, 06/18/2020:Matt*/

SELECT
p.PatNum,
pl.ProcDate,
pc.ProcCode,
CarrierName AS 'PriInsName',
pl.ProcFee,
(SELECT SUM(cp.InsPayEst) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (0,1,4)) AS '$InsPayEst',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPaidAmt'
FROM procedurelog pl
INNER JOIN patient p
ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc
ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp
ON pp.PatNum=p.PatNum
AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss
ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip
ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier
ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 -- Complete
AND carrier.carriername like '%Value Plan%'

GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

[ssebree]

Thank you, drtmz. I made the changes to this query, but unfortunately, it's not returning any results. We created our in-office plan through the Open Dental Discount Plan feature, so I'm wondering if it's not recognizing our plan as a carrier to match the term "carrier.carriername"?

[Tom Zaccaria]

It has to be called something. Use Main Menu, Lists, Carriers and see if it is listed.
Maybe support can give you a hint.

drtmz

[Tom Zaccaria]

How about this way?

When you want to assign a patient this computer generated plan and its fee schedule, what is it called in the insurance panel.
For example if they have Cigna it would be called Cigna PPO etc or MetLife.

drtmz

[allends]

This query is closer to what you are looking for ssebree. It is found on this page: https://opendentalsoft.com:1943/ODQuery ... yList.aspx

Code: Select all

/*1575 Patients with discount plans. For versions 20.5+*/

/*---------------------DO NOT MODIFY BELOW THIS LINE----------------------*/
/*Query code written/modified: 01/14/2021:SalinaK*/

SELECT
	p.PatNum AS 'PatID'
	,p.LName
	,p.FName
	,dp.Description AS 'Discount Plan'
FROM patient p
INNER JOIN discountplansub dps
	ON p.PatNum = dps.PatNum
INNER JOIN discountplan dp
	ON dps.DiscountPlanNum = dp.DiscountPlanNum
WHERE p.PatStatus NOT IN (3,4,5) -- Not archived/deleted/deceased
GROUP BY p.PatNum

The query only shows patients that have discount plans, so it lacks the information you want for business analytics, but it is a place to start.

[ssebree]

Thank you, Allen and drtmz, for the continued help with this. I'm finding that OD has created quite a silo around the Discount Plan feature. I can look up the fee schedule for our plan and can also see adjustments for each patient procedure of the plan. But there's nothing that puts all the information together (Patient + Production + Adjustment + Patient Income), nor is there anywhere I can search to piece this info together. I understand the logic behind using query 1575 as a guide, but OD has made all this info very disconnected and difficult to retrieve within the Discount Plan feature. I have someone else trying to help write the query on my end, so I'll keep you posted and let you know if we get it.

[ishubham2101]

Four years late, but this one never got answered and people still land on it, so here it is in full.

Query #1575 in the Query Examples list is a membership list. ssebree said twice what was missing from it: production, adjustments and patient income together, for the people on the plan. That is what this does.

One thing worth saying before the SQL, because it is the reason the query looks more complicated than it should. If you join procedurelog, adjustment and paysplit to patient in a single FROM clause and sum each column, the rows multiply. A patient with 4 procedures, 2 adjustments and 3 payments produces 24 rows, and every total is inflated by the row count of the other two tables. Production comes back roughly six times too high, and it looks plausible enough to act on. So each total below is its own subquery grouped by PatNum, joined afterwards.

The other useful thing: the per procedure discount the plan gave is on the procedure itself, procedurelog.DiscountPlanAmt. You do not have to go looking for it among the adjustments.

Code: Select all

SET @FromDate = '2026-01-01';
SET @ToDate   = '2026-12-31';

SELECT
    pat.PatNum,
    CONCAT(pat.LName, ', ', pat.FName)            AS patient,
    dp.Description                                AS discount_plan,
    COALESCE(prod.gross_production, 0)            AS gross_production,
    COALESCE(prod.plan_discount, 0)               AS plan_discount,
    COALESCE(prod.gross_production, 0) - COALESCE(prod.plan_discount, 0) AS net_production,
    COALESCE(adj.adjustments, 0)                  AS other_adjustments,
    COALESCE(pay.payments, 0)                     AS patient_payments,
    COALESCE(pay.payments, 0)
      - (COALESCE(prod.gross_production, 0) - COALESCE(prod.plan_discount, 0)
         + COALESCE(adj.adjustments, 0))          AS collected_minus_net,
    COALESCE(prod.procedures, 0)                  AS procedure_count
FROM discountplansub sub
    INNER JOIN discountplan dp  ON dp.DiscountPlanNum = sub.DiscountPlanNum
    INNER JOIN patient      pat ON pat.PatNum         = sub.PatNum
    LEFT JOIN (
        SELECT pl.PatNum,
               ROUND(SUM(pl.ProcFee), 2)          AS gross_production,
               ROUND(SUM(pl.DiscountPlanAmt), 2)  AS plan_discount,
               COUNT(*)                           AS procedures
        FROM procedurelog pl
        WHERE pl.ProcStatus = 2 AND pl.ProcDate BETWEEN @FromDate AND @ToDate
        GROUP BY pl.PatNum
    ) prod ON prod.PatNum = pat.PatNum
    LEFT JOIN (
        SELECT a.PatNum, SUM(a.AdjAmt) AS adjustments
        FROM adjustment a
        WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
        GROUP BY a.PatNum
    ) adj ON adj.PatNum = pat.PatNum
    LEFT JOIN (
        SELECT ps.PatNum, SUM(ps.SplitAmt) AS payments
        FROM paysplit ps
        WHERE ps.DatePay BETWEEN @FromDate AND @ToDate
        GROUP BY ps.PatNum
    ) pay ON pay.PatNum = pat.PatNum
WHERE (sub.DateTerm IS NULL OR YEAR(sub.DateTerm) < 1880 OR sub.DateTerm >= @FromDate)
  AND (sub.DateEffective IS NULL OR YEAR(sub.DateEffective) < 1880 OR sub.DateEffective <= @ToDate)
ORDER BY COALESCE(prod.gross_production, 0) DESC;

Assumptions, because these are the parts that will be wrong for somebody:

Production is completed procedures only, ProcStatus = 2, by ProcDate, summed as ProcFee with no unit multiplier. Adjustments are by AdjDate and signed as stored, so a writeoff is negative. Payments are patient payments from paysplit by DatePay, and insurance is not included, since a discount plan patient normally has none.

The plan membership fee is deliberately not separated out. Offices record it as a payment, an adjustment or a procedure code and nothing in the schema marks it, so I cannot tell which from outside. If you record it as a code, add that ProcCode as a fourth subquery and you have the whole picture.

Run on 26.2. If a column name has moved on your version it will tell you which one.

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