Query request

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
User avatar
DavidWolf
Posts: 259
Joined: Tue Jun 19, 2007 9:39 am
Location: Milford, MA
Contact:

Query request

Post by DavidWolf » Fri Sep 16, 2016 9:14 am

Does anyone have a query that shows a list of scheduled patients by date range with a specific insurance plan? Thanks
____________
Cheers,
Dave Wolf

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

Re: Query request

Post by cmcgehee » Mon Sep 19, 2016 11:07 am

This one will work if you only want to consider primary insurance carriers. Let me know if there are any minor modifications I can perform for you.

Code: Select all

/* Modified 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*/
/*Query code written/modified: 09/19/2016*/
SET @FromDate='2016-08-01' , @ToDate='2016-08-31';
SET @Carrier='%%';/*Set carrier here. Leave as '%%' to see all.*/
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*/
LEFT 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 a.AptDateTime BETWEEN @FromDate AND @ToDate + INTERVAL 1 DAY
AND a.AptStatus IN (1,2,4)
AND COALESCE(carrier.CarrierName,'*No Insurance') LIKE @Carrier
GROUP BY a.AptNum
ORDER BY a.AptDateTime;
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply