Fix query 403

For users or potential users.
Post Reply
jdub
Posts: 12
Joined: Mon Mar 02, 2015 10:09 am

Fix query 403

Post by jdub » Thu Apr 23, 2015 11:21 am

Does anyone want to fix query 403? This would be a very useful list for us, but we seem to get wrong answers for two of the results. It gives the patients coming for appointments for a given date range along with ins carrier, benefits remaining, benefits renewal date, and total fee for the appointment.

1. Benefits remaining: Our Medicaid patients have a July renewal. For all the insurance with calendar year renewal there is no problem, but for the Medicaid patients with July renewal the benefits remaining is miscalculated. What is returned is the benefits used with a negative sign. Presumably this is because the query is returning their renewal dates as being July 2014 instead of July 2015. I am guessing this is a problem with a calculation in the query and not our data, but I could be wrong.

2. The total fee for the day's services: I can't figure out what this number is. I can't find any numbers that add up to what is returned.

jdub
Posts: 12
Joined: Mon Mar 02, 2015 10:09 am

Re: Fix query 403

Post by jdub » Tue Sep 29, 2015 2:04 pm

This query used benefit.MonetaryAmt as the insurance annual maximum, which led to incorrect results for some patients in calculations for the amount of insurance benefits left for the year.

I changed this to MAX(benefit.MonetaryAmt), which I saw used in a different query, and this seemed to result in correct calculations for the benefits remaining.

Is this the correct way to get the annual maximum insurance benefit, or is there a better way? I couldn't really figure it out from the documentation of the database.

Thanks,
Jack

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Fix query 403

Post by jsalmon » Wed Sep 30, 2015 7:06 am

I'll have our query team take a look at 403. At the very least they might know of an alternative query that would be better to use.
jdub wrote:I changed this to MAX(benefit.MonetaryAmt), which I saw used in a different query, and this seemed to result in correct calculations for the benefits remaining.

Is this the correct way to get the annual maximum insurance benefit, or is there a better way? I couldn't really figure it out from the documentation of the database.
This is not a guaranteed way of getting the annual max but it's quick and I'd have to guess right nearly 90% (or more) of the time. Typically the benefit with the largest amount associated to it (at least with how Open Dental currently stores benefit information) will be the annual max. I would personally use it, however there is a more accurate way to get it but takes some time (ben type=limitation, covcat set to 0 or your ebenifit cat for general, quantity set to 0, etc, etc). Let me know if you're ever curious on the whole algorithm but it would be lengthy to write out correctly into a query when simply getting the MAX is fast and accurate (enough).
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Fix query 403

Post by tgriswold » Fri Oct 02, 2015 11:45 am

I have fixed query 403 on our website examples page, please take a look at it and let me know if there are any other issues. It is now much more specific about which benefit it uses as the annual max for the plan. As Jason mentioned max will work in almost all cases (because why would you have a limitation higher than annual max?) but this one is a little more elegant in its process.
Travis Griswold
Open Dental Software
http://www.opendental.com

jdub
Posts: 12
Joined: Mon Mar 02, 2015 10:09 am

Re: Fix query 403

Post by jdub » Tue Feb 07, 2017 10:20 am

tgriswold wrote:I have fixed query 403 on our website examples page, please take a look at it and let me know if there are any other issues. It is now much more specific about which benefit it uses as the annual max for the plan. As Jason mentioned max will work in almost all cases (because why would you have a limitation higher than annual max?) but this one is a little more elegant in its process.
Thanks for the fix. Your fix solved the problems I originally reported.

I have one more maybe small issue. I tried to add a column to this report by subtracting the discount from the total office fees, since this is what you really need to compare to the remaining benefits.

As a start I added this line:
SUM(pl.Discount) AS $Discount,

but everything in that column came back as zero.

Is Discount in procedurelog zero until the appointment is set complete or something like that? Does that information need to come out of proctp?

Thanks for your help again.

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Fix query 403

Post by cmcgehee » Tue Feb 07, 2017 11:04 am

The term 'discount' can mean two different things in Open Dental. There is a discount field that can be entered on a treatment plan procedure. When the procedure is set complete, an adjustment is created for the same amount as the discount. A discount can also mean 'writeoff', as in a PPO discount. This is the difference between your office fees and the carrier fees when the insurance type is PPO. This value is displayed in the Discount column in the Treatment Plan module. Your query is summing of discounts in the former sense whereas it sounds like you want discounts in the latter sense. To sum up PPO discounts, you can look at the WriteoffEst and WriteoffEstOverride columns on the claimproc table. You can simply replace
SUM(pl.Discount) AS $Discount
with these lines
SUM((SELECT SUM(CASE WHEN cp.WriteOffEstOverride=-1 THEN (CASE WHEN cp.WriteOffEst=-1 THEN cp.WriteOff ELSE (cp.WriteOffEst) END) ELSE (cp.WriteOffEstOverride)END)
FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum)) AS $WriteoffEst
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply