Query request
Posted: 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
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;