Open Dental Forum

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;

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.

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: 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.

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!