New Patients and the Insurance they come in with

For users or potential users.
Post Reply
djanash@mac.com
Posts: 59
Joined: Sat Mar 13, 2010 11:11 am

New Patients and the Insurance they come in with

Post by djanash@mac.com » Wed May 15, 2013 4:30 pm

How can I generate a list for All the patients for the month and the Dental insurance Carrier/Plan they came in with.
I cant seem to find a query for it #648 only does one at a time.

zt22
Posts: 20
Joined: Fri Nov 06, 2009 12:44 pm

Re: New Patients and the Insurance they come in with

Post by zt22 » Wed May 22, 2013 2:08 pm

I have the following query which lists insurance for New Patients. You would need to change the data range in the first line. One possible issue is that the query uses the DateFirstVisit to identify new patients. It seems that if a new patient schedules once before and broke the appointment, the DateFirstVisit is still set to the date of the broken appointment, so if a new patient who has a broken appointment reschedules, this query will not list them as new patients. I can't remember if that is the case with this query, but you may want to compare it with the built-in new patient report to verify.

/* List of NEW 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-05-01' , @ToDate='2013-05-31';
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) AND DATE(a.AptDateTime)=p.DateFirstVisit
GROUP BY a.AptNum
ORDER BY a.AptDateTime;

Post Reply