Insurance Plan Report - Current

For users or potential users.
Post Reply
Annesa94
Posts: 18
Joined: Wed Sep 23, 2015 4:05 pm

Insurance Plan Report - Current

Post by Annesa94 » Thu Oct 12, 2017 7:06 am

Hi All. We are considering the ramifications of dropping an insurance network. When we run a report by insurance plan of this particular network it appears to show anyone who ever had the specific plan versus patients on the plan at the present time. In other words, patient names are coming up with this plan in the report list, but if we go into their account they have either a different insurance now, no insurance etc... Aside from going through each name and looking up each individual, is there a way around this/a way to run a report of current patients with this plan as active? Thanks in advance.

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

Re: Insurance Plan Report - Current

Post by cmcgehee » Thu Oct 12, 2017 7:50 am

The internal insurance plans report includes patients with inactive plans. To get the information you're looking for, I would submit a query request on our website: http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx.
Chris McGehee
Open Dental Software
http://www.opendental.com

Annesa94
Posts: 18
Joined: Wed Sep 23, 2015 4:05 pm

Re: Insurance Plan Report - Current

Post by Annesa94 » Thu Oct 12, 2017 8:06 am

Thank you! Will do.

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: Insurance Plan Report - Current

Post by rhaber123 » Fri Oct 13, 2017 1:41 pm

We use this modified query #356 for end of year. It includes email, phone numbers and addresses, if you want to contact or mail your patients
After you export the results to an excel documents, you can sort it by the insurance names
adjust the dates accordingly

Code: Select all

 
/*356 List of Patients that have not been in since a specific date that have insurance benefits remaining.  

Includes Insurance Carrier's Name, Patient's Name, Three Phone Numbers, Benefits Remaining,

TP Remaining and Last seen date. Assumes everyone has calendar year benefits*/

SET @FromDate='2017-12-28';

SELECT p.PatNum AS 'Pat#',

p.LName,  p.FName, p.HmPhone, p.WkPhone, p.WirelessPhone, p.Email,

p. Address, p.Address2, p.City, p.State, p.Zip,

DATE_FORMAT(lastseen.LastSeen,'%m/%d/%Y')AS LastSeen,

annualmax.AnnualMax '$AnnualMax_',

used.AmtUsed '$AmountUsed_',

annualmax.AnnualMax-COALESCE(used.AmtUsed,0) '$AmtRemaining_',

c.CarrierName

FROM patient p

INNER JOIN patplan ON p.PatNum=patplan.PatNum

INNER JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum

INNER JOIN insplan ip ON ip.PlanNum=inssub.PlanNum

INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum

INNER JOIN (

 SELECT benefit.PlanNum, 

 MAX(benefit.MonetaryAmt) AS AnnualMax

 FROM benefit

 LEFT JOIN covcat ON covcat.CovCatNum = benefit.CovCatNum

 WHERE benefit.BenefitType = 5 /* limitation */

 AND benefit.TimePeriod = 2 /* calendar year */

 AND (covcat.EbenefitCat=1 OR ISNULL(covcat.EbenefitCat))

 AND benefit.MonetaryAmt > 0 /* (-1) indicates empty */

 GROUP BY benefit.PlanNum

) annualmax ON annualmax.PlanNum=inssub.PlanNum

LEFT JOIN ( 

 SELECT patplan.PatPlanNum,

 SUM(claimproc.InsPayAmt) AS AmtUsed

 FROM claimproc

 INNER JOIN inssub ON claimproc.InsSubNum=inssub.InsSubNum

 INNER JOIN patplan ON inssub.InsSubNum=patplan.InsSubNum 

  AND patplan.PatNum=claimproc.PatNum

 WHERE claimproc.Status IN (1, 3, 4) -- Rec, Adj, Supp.

 AND YEAR(claimproc.ProcDate)=YEAR(CURDATE())

 AND claimproc.InsPayAmt!=0

 GROUP BY patplan.PatPlanNum

) used ON used.PatPlanNum=patplan.PatPlanNum

INNER JOIN (

 SELECT p.PatNum,

 MAX(procdate) AS LastSeen

 FROM patient p

 INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum

 AND pl.ProcStatus=2 /*complete*/

 AND p.PatStatus=0 /*active patient*/

 GROUP BY pl.PatNum

) lastseen ON lastseen.PatNum=p.PatNum AND lastseen.LastSeen<@FromDate

WHERE PatStatus=0

ORDER BY c.CarrierName;


Last edited by rhaber123 on Fri Oct 13, 2017 2:02 pm, edited 3 times in total.

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: Insurance Plan Report - Current

Post by rhaber123 » Fri Oct 13, 2017 1:48 pm

Query #71 Patients' Names and Insurance Names only
adjust the dates accordingly

Code: Select all


/*71*/ SET @Start='2000-01-01' , @Before='2017-12-31';

/*patients name and insurance Adjust above Dates as needed*/

SET @pos=0; 

SELECT @pos:=@pos+1 AS Count, carrier.CarrierName, patient.PatNum 

FROM carrier, insplan, claim, claimproc, patient

WHERE insplan.PlanNum=claim.PlanNum AND

insplan.CarrierNum=carrier.CarrierNum AND

claimproc.ClaimNum=claim.ClaimNum AND

claimproc.PatNum=patient.PatNum AND

/*this is in case we need additional patient information*/ 

/*you can add any patient or insurance fields to the SELECT statement*/

ProcDate >= @Start AND

ProcDate < @Before

GROUP BY patient.PatNum, CarrierName

ORDER BY CarrierName;




Last edited by rhaber123 on Fri Oct 13, 2017 2:02 pm, edited 1 time in total.

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: Insurance Plan Report - Current

Post by rhaber123 » Fri Oct 13, 2017 1:58 pm

query #69 ----/*69 Count of patients by Carrier with procedures completed in date range*/
change the dates accordingly

Code: Select all

   

/*69 Count of patients by Carrier with procedures 
completed in date range*/
SET @FromDate='2000-01-01' , @ToDate='2017-12-31';
/*Adjust above Dates AS needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients', carrier.Phone
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum 
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum 
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND (procedurelog.ProcDate BETWEEN @FromDate AND @ToDate)
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;







rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: Insurance Plan Report - Current

Post by rhaber123 » Fri Oct 13, 2017 2:01 pm

Income by insurance company for date range

Code: Select all

   

/*70*/ SET @Start='2017-01-01' , @Before='2017-12-30';
/*Adjust above Dates as needed*/
SELECT CarrierName, SUM(CheckAmt) AS $Income
FROM claimpayment
WHERE CheckDate >= @Start
AND CheckDate < @Before
GROUP BY CarrierName; 



Post Reply