Bug in Pt's w/ apt search (#247) and #290

For users or potential users.
Post Reply
User avatar
cheroxy
Posts: 57
Joined: Mon Aug 13, 2007 5:52 pm
Contact:

Bug in Pt's w/ apt search (#247) and #290

Post by cheroxy » Thu Mar 14, 2013 11:59 am

It looks like if a person doesn't have an insurance company the report should show "No Insurance" but it doesn't include them in the report. Am I reading this incorrectly? Code below.

Also, query 290 returns a bug w/o any editing.

/* 247 List of patients with appointments for a date range (in future or past) with primary insurance carrier listed
Also lists sum of fees for day and insurance type*/
SET @FromDate='2013-03-27' , @ToDate='2013-03-27';
SELECT p.PatNum, a.AptDateTime,
(CASE WHEN ISNULL(carrier.CarrierName) THEN '*No Insurance' ELSE (carrier.CarrierName) END) AS 'PriCarrier', SUM(pl.ProcFee) AS '$Fees',
(CASE WHEN ip.PlanType='' THEN 'Category Percentage'
WHEN ip.PlanType='p' THEN 'PPO'
WHEN ip.PlanType='f' THEN 'FlatCopay'
WHEN ip.PlanType='c' THEN 'Capitation'
ELSE 'Unknown' END) AS PlanType
FROM appointment a
INNER JOIN procedurelog pl ON a.AptNum=pl.AptNum
LEFT JOIN patient p ON p.PatNum=a.PatNum /*just in case we need field FROM patient table*/
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
INNER JOIN inssub ON pp.InsSubNum=inssub.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE (DATE(a.AptDateTime) BETWEEN @FromDate AND @ToDate) AND a.AptStatus IN (1,2,4)
GROUP BY a.AptNum
ORDER BY a.AptDateTime;

bpcomp
Posts: 304
Joined: Mon Feb 27, 2012 7:30 am
Location: Tucson, AZ
Contact:

Re: Bug in Pt's w/ apt search (#247) and #290

Post by bpcomp » Thu Mar 14, 2013 2:52 pm

I was able to replicate and see that it does not return any results for patients who don't have insurance. I wasn't able to figure out why though.

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

Re: Bug in Pt's w/ apt search (#247) and #290

Post by jsalmon » Thu Mar 14, 2013 7:31 pm

I'm not sure if that's what the person that wrote the query had in mind but I can tell you that your culprit is:

Code: Select all

INNER JOIN inssub ON pp.InsSubNum=inssub.InsSubNum
To not restrict users that don't have insurance you probably have to change that from an inner join to a LEFT join. I haven't given much thought to that change so I can't guarantee the results will be desirable, just noticed it at a glance.
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

User avatar
cheroxy
Posts: 57
Joined: Mon Aug 13, 2007 5:52 pm
Contact:

Re: Bug in Pt's w/ apt search (#247) and #290

Post by cheroxy » Fri Mar 15, 2013 9:10 am

That worked. I wanted a search to determine the number of patients that I have with appointments for my accountants evaluation of our practice. This will now give me that!

Post Reply