Custom Query for Discount Plan

For users or potential users.
Post Reply
ssebree
Posts: 5
Joined: Thu Mar 24, 2022 8:24 am

Custom Query for Discount Plan

Post by ssebree » Thu Mar 24, 2022 11:08 am

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
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Custom Query for Discount Plan

Post by allends » Thu Mar 24, 2022 11:20 am

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
Allen
Open Dental Software
http://www.opendental.com

ssebree
Posts: 5
Joined: Thu Mar 24, 2022 8:24 am

Re: Custom Query for Discount Plan

Post by ssebree » Tue Mar 29, 2022 2:59 pm

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
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Custom Query for Discount Plan

Post by allends » Tue Mar 29, 2022 3:18 pm

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.
Allen
Open Dental Software
http://www.opendental.com

ssebree
Posts: 5
Joined: Thu Mar 24, 2022 8:24 am

Re: Custom Query for Discount Plan

Post by ssebree » Tue Mar 29, 2022 3:23 pm

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
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Custom Query for Discount Plan

Post by Tom Zaccaria » Wed Mar 30, 2022 5:13 am

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
Posts: 5
Joined: Thu Mar 24, 2022 8:24 am

Re: Custom Query for Discount Plan

Post by ssebree » Wed Mar 30, 2022 2:15 pm

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
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Custom Query for Discount Plan

Post by Tom Zaccaria » Thu Mar 31, 2022 2:43 am

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
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Custom Query for Discount Plan

Post by Tom Zaccaria » Thu Mar 31, 2022 3:36 am

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
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Custom Query for Discount Plan

Post by allends » Thu Mar 31, 2022 8:57 am

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.
Allen
Open Dental Software
http://www.opendental.com

ssebree
Posts: 5
Joined: Thu Mar 24, 2022 8:24 am

Re: Custom Query for Discount Plan

Post by ssebree » Sat Apr 02, 2022 10:43 pm

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.

Post Reply