Home User Manual Discussion Forum Search

EHR Eligibility Queries 

Existing Open Dental customers can Run Queries to determine eligibility for the EHR Incentive Program or to provide supporting documentation during Attestation for an incentive payment.

To run a query, copy the query text (Ctrl + C), then paste it into the Query window. You may need to change information such as date range, NPI number, carrier, etc. It may be useful to export query results to Excel so you can format the results the way you want.

Hint:  To add identifying header information to query results, first save the query to your Query Favorites, then enter a Title that includes all identifying information (e.g. title, date ranges, provider). Then run the query again. If you change report criteria, make sure to change the query's title also.

1. Percentage of Patient Encounters, by NPI
Percentage of patient encounters by insurance carrier, for a date range, by NPI. Use to determine Medicaid patient volume for a specific time frame. The percentage may add up to over 100% if a single patient has multiple carriers on the same day of service.

/*973 EHR query - Percentage of Patient Encounters by NPI*/
SET @FromDate='2015-01-01' , @ToDate='2015-12-31'; /*Adjust dates as needed*/
SET @ProvNPI='1234567890'; /*Set provider NPI # here*/
/*Percent of appointments by insurance carrier, for each provider, and the medicaid percentages for a time period to determine EHR eligibility.
Count and % of appointments (or dates where procedures were completed) by carrier and provider over given period.
If two providers in the DB have the same NPI, their results will be combined in this report. Because of that, the appointment and patients count may differ
from the EHR query split by provider if a patient saw two different providers in the database that have the same NPI on the same date.
Does not count D9986, D9987, and N#### codes as appointments.
Distinct Office Totals are the total number of unique appointments and patients seen in the reporting period regardless of insurance carrier. This is the
denominator used to calculate "% of visits (by Provider)"*/
(SELECT GROUP_CONCAT(DISTINCT A.Abbr ORDER BY A.Abbr SEPARATOR ', ') AS 'ProvAbbr',
A.NationalProvID,
A.CarrierName,
A.GroupName,
COUNT(DISTINCT A.PatNum) AS 'Patients',
COUNT(DISTINCT A.ProcDate,A.PatNum,A.NationalProvID) AS Appointments,
FORMAT((COUNT(DISTINCT A.ProcDate,A.PatNum,A.NationalProvID)/(
SELECT COUNT(DISTINCT B.ProcDate,B.PatNum,B.NationalProvID)
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,prov.NationalProvID
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
GROUP BY prov.NationalProvID,ca.CarrierName,pl.PatNum,pl.ProcDate,pl.ProcNum,ip.GroupName
UNION ALL
/*Where claims do NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,prov.NationalProvID
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
GROUP BY prov.NationalProvID,/*ca.CarrierName,*/pl.PatNum,pl.ProcDate,pl.ProcNum/*,ip.GroupName*/
) B)
)*100,2) AS '% of visits (by Provider)'
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,prov.NationalProvID,prov.Abbr
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
GROUP BY prov.NationalProvID,ca.CarrierName,pl.PatNum,pl.ProcDate,pl.ProcNum,ip.GroupName
UNION ALL
/*Where claims do NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,prov.NationalProvID,prov.Abbr
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
GROUP BY prov.NationalProvID,/*ca.CarrierName,*/pl.PatNum,pl.ProcDate,pl.ProcNum/*,ip.GroupName*/
) A
GROUP BY A.NationalProvID,A.CarrierName,A.GroupName
ORDER BY A.NationalProvID,A.CarrierName,A.GroupName)

UNION ALL

(SELECT '' AS 'ProvAbbr',
'' AS NationalProvID,
'Distinct Office Total',
'',
COUNT(DISTINCT B.PatNum) AS 'Patients',
COUNT(DISTINCT B.ProcDate,B.PatNum,B.NationalProvID) AS 'Appointments',
'' AS '% of visits (by Provider)'
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,prov.NationalProvID
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
GROUP BY prov.NationalProvID,ca.CarrierName,pl.PatNum,pl.ProcDate,pl.ProcNum,ip.GroupName
UNION ALL
/*Where claims do NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,prov.NationalProvID
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
GROUP BY prov.NationalProvID,/*ca.CarrierName,*/pl.PatNum,pl.ProcDate,pl.ProcNum/*,ip.GroupName*/
) B);

2. Percentage of Patient Encounters for all Providers
Percentage of patient encounters by insurance carrier, for a date range, for all providers. Use to determine Medicaid patient volume for a specific time frame. The percentage may add up to over 100% if a single patient was seen by multiple carriers on the same day of service.

/*974 EHR query - Percentage of Patient Encounters for all Providers*/
SET @FromDate='2015-01-01' , @ToDate='2015-12-31'; /*Adjust dates as needed*/
/*Percent of appointments by insurance carrier, for each provider, and the medicaid percentages for a time period to determine EHR eligibility.
Count and % of appointments (or dates where procedures were completed) by carrier and provider over given period
Does not count D9986, D9987, and N#### codes as appointments.*/
SELECT A.ProvNum,
A.CarrierName,
A.GroupName,
COUNT(DISTINCT A.PatNum) AS 'Patients',
COUNT(DISTINCT A.ProcDate,A.PatNum,A.ProvNum) AS Appointments,
FORMAT((COUNT(DISTINCT A.ProcDate,A.PatNum,A.ProvNum)/(
SELECT COUNT(DISTINCT B.ProcDate,B.PatNum,B.ProvNum)
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
GROUP BY pl.ProvNum,ca.CarrierName,pl.PatNum,pl.ProcDate,pl.ProcNum
UNION ALL
/*Where claims do NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
GROUP BY pl.ProvNum,/*ca.CarrierName,*/pl.PatNum,pl.ProcDate,pl.ProcNum
) B
WHERE B.ProvNum=A.ProvNum)
)*100,2) AS '% of visits (by Provider)'
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
GROUP BY pl.ProvNum,ca.CarrierName,pl.PatNum,pl.ProcDate,pl.ProcNum
UNION ALL
/*Where claims do NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
GROUP BY pl.ProvNum,/*ca.CarrierName,*/pl.PatNum,pl.ProcDate,pl.ProcNum
) A
GROUP BY A.ProvNum,A.CarrierName,A.GroupName
ORDER BY A.ProvNum,A.CarrierName,A.GroupName;

3. Percentage of Patient Encounters - Recent Conversion
For providers who had a recent data conversion. Percentage of patient encounters by insurance carrier, for a date range, for all providers. Use to determine Medicaid patient volume for a specific time frame. The percentage may add up to over 100% if a single patient was seen by multiple carriers on the same day of service.

/*975 EHR Query - Percentage of Patient Encounters - Recent Conversion*/
SET @FromDate='2011-01-01' , @ToDate='2011-03-31';
/*Percent of appointments by insurance carrier, for each provider, and the medicaid percentages for a time period to determine EHR eligibility (this query works for current Open Dental users as well as post conversion and trial conversions: if there was no claim for a particular procedure, assumes if patient has insurance currently that the patient had it at time of appointment): Count and % of appointments (or dates where procedures were completed) by carrier over given period, adds up to more or less than 100% because there can be more than one carrier per patient and because non insurance patients are not on the list (although their appointments are part of the denominator for the % appointments)
Does not count D9986, D9987, and N#### codes as appointments.*/
/*Adjust above Dates AS needed*/
SELECT A.ProvNum,A.CarrierName,A.GroupName,COUNT(DISTINCT A.PatNum) AS 'Patients',
COUNT(DISTINCT A.ProcDate,A.PatNum,A.ProvNum) AS Appointments,
FORMAT((COUNT(DISTINCT A.ProcDate,A.PatNum,A.ProvNum)/(
SELECT COUNT(DISTINCT B.ProcDate,B.PatNum,B.ProvNum)
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
UNION ALL
/*Where claims do NOT exist but insurance currently does
Could count visit twice if patient currently has more than one ins plan*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patplan pp ON pp.PatNum=pl.PatNum
INNER JOIN inssub ib ON pp.InsSubNum=ib.InsSubNum
INNER JOIN insplan ip ON ib.PlanNum=ip.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
UNION ALL
/*Where claims do NOT exist AND insurance currently does NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
LEFT JOIN patplan pp ON pp.PatNum=pl.PatNum/*No patplan entry=no current ins*/
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
AND ISNULL(pp.PatNum)/*No current insurance plan for this patient*/
) B
WHERE B.ProvNum=A.ProvNum)
)*100,1) AS '% of visits (by Provider)'
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
UNION ALL
/*Where claims do NOT exist but insurance currently does
Could count visit twice if patient currently has more than one ins plan*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patplan pp ON pp.PatNum=pl.PatNum
INNER JOIN inssub ib ON pp.InsSubNum=ib.InsSubNum
INNER JOIN insplan ip ON ib.PlanNum=ip.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
UNION ALL
/*Where claims do NOT exist AND insurance currently does NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
LEFT JOIN patplan pp ON pp.PatNum=pl.PatNum/*No patplan entry=no current ins*/
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
AND ISNULL(pp.PatNum)/*No current insurance plan for this patient*/
) A
GROUP BY A.ProvNum,A.CarrierName,A.GroupName
ORDER BY A.ProvNum,A.CarrierName,A.GroupName;

4. Claim Details by NPI
Claim details for a date range, by NPI. Use with a Percentage of Patient Encounters query to see patient claim information.

/*976 EHR Query - Split by Encounter - Claim Details by NPI*/
SET @FromDate='2015-01-01' , @ToDate='2015-03-31'; /*<----Adjust Dates AS needed*/
SET @ProvNPI='1234567890'; /*Set provider NPI # here*/
/*Provider,Carrier,Patient,Subscriber ID, and Date of Service in Date Range with Procedure Fees.
Does not count D9986, D9987, and N#### codes as encounters.
There may or may not be a claim for the procedures with the given carrier, proc fees, and date of service.
A patient may also have more than one insurance carrier, as in primary and secondary insurance. In this case, the procedure fees will appear in the
$ProcFees_ column for both insurance carriers. There may be more than one subscriberID in a concatenated list of ID's.
If there is not a claim for the procedures the carrier name will be 'NoIns' and the SubscriberIDs will be N/A.
If two providers in the DB have the same NPI, their results will be combined in this report.*/
SELECT GROUP_CONCAT(DISTINCT B.ProvAbbr ORDER BY B.ProvAbbr SEPARATOR ', ') AS 'ProvAbbr', B.NationalProvID,
B.CarrierName,B.GroupName,B.PatNum,DATE_FORMAT(B.BirthDate,'%m/%d/%Y') AS 'BirthDate',B.SubscriberIDs,B.ProcDate,
SUM(CASE WHEN ISNULL(pl.ProcFee) THEN 0 ELSE (pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) END) AS $ProcFees_,
SUM(B.InsPayAmt) AS $InsPayAmt_, GROUP_CONCAT(DISTINCT B.ProcCodes ORDER BY B.ProcCodes SEPARATOR ', ') AS 'ProcCodes'
FROM (
SELECT A.ProvNum,A.CarrierName,A.GroupName,A.PatNum,
GROUP_CONCAT(DISTINCT A.SubscriberID) AS SubscriberIDs,
A.ProcDate,A.ProcNum,SUM(A.InsPayAmt) AS InsPayAmt,A.NationalProvID,GROUP_CONCAT(DISTINCT A.Abbr ORDER BY A.Abbr SEPARATOR ', ') AS 'ProvAbbr',A.Birthdate,GROUP_CONCAT(DISTINCT A.ProcCode) AS ProcCodes
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,
ib.SubscriberID AS SubscriberID,pl.ProcNum,cp.InsPayAmt AS InsPayAmt,prov.NationalProvID,prov.Abbr,p.Birthdate,pc.ProcCode
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
UNION ALL
/*Insurance payments not split by procedure*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,cp.PatNum,cp.ProcDate,cp.ProvNum,
ib.SubscriberID AS SubscriberID,cp.ProcNum,cp.InsPayAmt AS InsPayAmt,prov.NationalProvID,prov.Abbr,p.Birthdate,'InsPaymentNotSplit' AS ProcCode
FROM claimproc cp
INNER JOIN patient p ON p.PatNum=cp.PatNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
INNER JOIN provider prov ON prov.ProvNum=cp.ProvNum
AND prov.NationalProvID=@ProvNPI
WHERE cl.DateService BETWEEN @FromDate AND @ToDate
AND cp.ProcNum=0
UNION ALL
/*Where claims do NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,
'N/A' AS SubscriberID,pl.ProcNum,0 /*No InsPayAmt*/,prov.NationalProvID,prov.Abbr,p.Birthdate,pc.ProcCode
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
AND prov.NationalProvID=@ProvNPI
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
) A
GROUP BY A.NationalProvID,A.CarrierName,A.PatNum,A.ProcDate,A.ProcNum,A.GroupName
) B
LEFT JOIN procedurelog pl ON pl.ProcNum=B.ProcNum
GROUP BY B.NationalProvID,B.CarrierName,B.PatNum,B.ProcDate,B.GroupName
ORDER BY B.NationalProvID,B.CarrierName,B.ProcDate;

5. Claim Details for all Providers
Claim details for a date range for all providers. Use with a Percentage of Patient Encounters query to see patient claim information.

/*977 EHR Query - Split by Encounter - Claim Details for all Providers*/
SET @FromDate='2015-01-01' , @ToDate='2015-12-31'; /*<----Adjust Dates AS needed*/
/*Provider,Carrier,Patient,Subscriber ID, and Date of Service in Date Range with Procedure Fees.
Does not count D9986, D9987, and N#### codes as encounters.
There may or may not be a claim for the procedures with the given carrier, proc fees, and date of service.
A patient may also have more than one insurance carrier, as in primary and secondary insurance. In this case, the procedure fees will appear in the
$ProcFees_ column for both insurance carriers. There may be more than one subscriberID in a concatenated list of ID's.
If there is not a claim for the procedures the carrier name will be 'NoIns' and the SubscriberIDs will be N/A.*/
SELECT B.ProvNum,B.CarrierName,B.GroupName,B.PatNum,DATE_FORMAT(B.BirthDate,'%m/%d/%Y') AS 'BirthDate',B.SubscriberIDs,B.ProcDate,
SUM(CASE WHEN ISNULL(pl.ProcFee) THEN 0 ELSE (pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) END) AS $ProcFees_,
SUM(B.InsPayAmt) AS $InsPayAmt_, GROUP_CONCAT(DISTINCT B.ProcCodes ORDER BY B.ProcCodes SEPARATOR ', ') AS 'ProcCodes'
FROM (
SELECT A.ProvNum,A.CarrierName,A.GroupName,A.PatNum,
GROUP_CONCAT(DISTINCT A.SubscriberID) AS SubscriberIDs,
A.ProcDate,A.ProcNum,SUM(A.InsPayAmt) AS InsPayAmt,A.Birthdate,GROUP_CONCAT(DISTINCT A.ProcCode) AS ProcCodes
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,
ib.SubscriberID AS SubscriberID,pl.ProcNum,cp.InsPayAmt AS InsPayAmt,p.Birthdate,pc.ProcCode
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
UNION ALL
/*Insurance payments not split by procedure*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,cp.PatNum,cp.ProcDate,cp.ProvNum,
ib.SubscriberID AS SubscriberID,cp.ProcNum,cp.InsPayAmt AS InsPayAmt,p.Birthdate,'InsPaymentNotSplit' AS ProcCode
FROM claimproc cp
INNER JOIN patient p ON p.PatNum=cp.PatNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE cl.DateService BETWEEN @FromDate AND @ToDate
AND cp.ProcNum=0
UNION ALL
/*Where claims do NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,
'N/A' AS SubscriberID,pl.ProcNum,0 /*No InsPayAmt*/,p.Birthdate,pc.ProcCode
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patient p ON p.PatNum=pl.PatNum
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
) A
GROUP BY A.ProvNum,A.CarrierName,A.PatNum,A.ProcDate,A.ProcNum,A.GroupName
) B
LEFT JOIN procedurelog pl ON pl.ProcNum=B.ProcNum
GROUP BY B.ProvNum,B.CarrierName,B.PatNum,B.ProcDate,B.GroupName
ORDER BY B.ProvNum,B.CarrierName,B.ProcDate;

6. Claim Details - Recent Conversion
For providers who had a recent data conversion. Claim details for a date range, for all providers. Use with a Percentage of Patient Encounters after a Conversion query to see patient claim information.

/*978 EHR Query - Split by Encounter - Claim Details Recent Conversion*/
SET @FromDate='2013-01-05' , @ToDate='2013-01-07';/*<----Adjust Dates AS needed*/
/*Provider,Carrier,Patient,Subscriber ID, and Date of Service in Date Range with Procedure Fees.
Does not count D9986, D9987, and N#### codes as encounters.
There may or may not be a claim for the procedures with the given carrier, proc fees, and date of service.
We have to include procedures completed in the date range if the patient currently has an insurance plan,
but may or may not have had it at the time of the procedures. A patient may also have more than one
insurance carrier, as in primary and secondary insurance. In this case, the procedure fees will appear in the
$ProcFees_ column for both insurance carrier. There may be more than one subscriberID in a concatenated list of ID's. If there is not a claim for the procedures and the patient does not currently have insurance, the carrier
name will be 'NoIns' and the SubscriberIDs will be N/A.*/
SELECT B.ProvNum,B.CarrierName,B.GroupName,B.PatNum,DATE_FORMAT(B.BirthDate,'%m/%d/%Y') AS 'BirthDate',B.SubscriberIDs,B.ProcDate,
SUM(CASE WHEN ISNULL(pl.ProcFee) THEN 0 ELSE (pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) END) AS $ProcFees_,
SUM(B.InsPayAmt) AS $InsPayAmt_, GROUP_CONCAT(DISTINCT B.ProcCodes ORDER BY B.ProcCodes SEPARATOR ', ') AS 'ProcCodes'
FROM (
SELECT A.ProvNum,A.CarrierName,A.GroupName,A.PatNum,
GROUP_CONCAT(DISTINCT A.SubscriberID) AS SubscriberIDs,
A.ProcDate,A.ProcNum,SUM(A.InsPayAmt) AS InsPayAmt,A.Birthdate,GROUP_CONCAT(DISTINCT A.ProcCode) AS ProcCodes
FROM (
/*Where claim exists, considered an encounter for carrier on the claim*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,
ib.SubscriberID AS SubscriberID,pl.ProcNum,cp.InsPayAmt AS InsPayAmt,p.Birthdate,pc.ProcCode
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
UNION ALL
/*Insurance payments not split by procedure*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,cp.PatNum,cp.ProcDate,cp.ProvNum,
ib.SubscriberID AS SubscriberID,cp.ProcNum,cp.InsPayAmt AS InsPayAmt,p.Birthdate,'InsPaymentNotSplit' AS ProcCode
FROM claimproc cp
INNER JOIN patient p ON p.PatNum=cp.PatNum
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum AND cl.ClaimType!='PreAuth'
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.InsSubNum=cl.InsSubNum
WHERE cl.DateService BETWEEN @FromDate AND @ToDate
AND cp.ProcNum=0
UNION ALL
/*Where claims do NOT exist but insurance currently does*/
SELECT ca.CarrierName AS CarrierName,ip.GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,
ib.SubscriberID AS SubscriberID,pl.ProcNum,0 AS InsPayAmt,p.Birthdate,pc.ProcCode
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN patplan pp ON pp.PatNum=pl.PatNum
INNER JOIN inssub ib ON pp.InsSubNum=ib.InsSubNum
INNER JOIN insplan ip ON ib.PlanNum=ip.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
UNION ALL
/*Where claims do NOT exist AND insurance currently does NOT exist*/
SELECT 'NoIns' AS CarrierName,'N/A' AS GroupName,pl.PatNum,pl.ProcDate,pl.ProvNum,
'N/A' AS SubscriberID,pl.ProcNum,0 /*No InsPayAmt*/,p.Birthdate,pc.ProcCode
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode NOT IN ('D9986', 'D9987')
AND pc.ProcCode NOT LIKE 'N%'
INNER JOIN patient p ON p.PatNum=pl.PatNum
LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
LEFT JOIN patplan pp ON pp.PatNum=pl.PatNum/*No patplan entry=no current ins*/
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2/*Complete*/
AND (ISNULL(cp.ProcNum) OR cp.ClaimNum=0)/*No claim attached to this procedure*/
AND ISNULL(pp.PatNum)/*No current insurance plan for this patient*/
) A
GROUP BY A.ProvNum,A.CarrierName,A.PatNum,A.ProcDate,A.ProcNum,A.GroupName
) B
LEFT JOIN procedurelog pl ON pl.ProcNum=B.ProcNum
GROUP BY B.ProvNum,B.CarrierName,B.PatNum,B.ProcDate,B.GroupName
ORDER BY B.ProvNum,B.CarrierName,B.ProcDate;

7. Texas Medicaid Patient Volume Query
For EHR providers who must provide supporting documentation for patient volume when attesting with Texas Medicaid. 

/*EHR Attestation, Texas Medicaid. "Supporting Documentation for Patient Volume"*/
/*Set date range and carrier name at top*/
SET @FromDate='2013-01-01', @ToDate='2014-12-31';
SET @CarrierName='%Medicaid%';
SELECT 'Open Dental Software' AS 'Source of Patient Volume Report',
p.MedicaidID AS 'Medicaid Client Number',
cl.DateService AS 'Date of Service',
(CASE WHEN cl.ClaimStatus='R' THEN (CASE WHEN cl.InsPayAmt=0 THEN 'Denied' ELSE 'Paid' END) WHEN cl.ClaimStatus='S' THEN 'Not Received' ELSE 'Not Sent' END) AS 'Claim Status',
(CASE WHEN pt.NationalProvID='' THEN 'NPI not entered' ELSE pt.NationalProvID END) AS 'Performing Provider NPI',
(CASE WHEN pb.NationalProvID='' THEN 'NPI not entered' ELSE pb.NationalProvID END) AS 'Billing NPI',
(CASE WHEN pb.UsingTIN=1 THEN pb.SSN ELSE 'TIN not entered' END) AS 'Billing TIN'
FROM claim cl
INNER JOIN patient p ON p.PatNum = cl.PatNum
INNER JOIN provider pt ON pt.ProvNum=cl.ProvTreat
INNER JOIN provider pb ON pb.ProvNum=cl.ProvBill
INNER JOIN insplan ip ON ip.PlanNum=cl.PlanNum
INNER JOIN carrier car ON ip.CarrierNum=car.CarrierNum
WHERE cl.ClaimType!='PreAuth'
AND cl.DateService BETWEEN @FromDate AND @ToDate
AND car.CarrierName LIKE @CarrierName
ORDER BY cl.DateService, pt.NationalProvID

 

Open Dental Software 1-503-363-5432