Insurance Plan Report - Current

For users or potential users.

Insurance Plan Report - Current

Postby 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.
Annesa94
 
Posts: 18
Joined: Wed Sep 23, 2015 4:05 pm

Re: Insurance Plan Report - Current

Postby 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/ODQueryRequest/QueryRequestForm.aspx.
Chris McGehee
Open Dental Software
http://www.opendental.com
User avatar
cmcgehee
 
Posts: 243
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Insurance Plan Report - Current

Postby Annesa94 » Thu Oct 12, 2017 8:06 am

Thank you! Will do.
Annesa94
 
Posts: 18
Joined: Wed Sep 23, 2015 4:05 pm

Re: Insurance Plan Report - Current

Postby 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: 257
Joined: Fri Dec 11, 2009 1:09 pm

Re: Insurance Plan Report - Current

Postby 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: 257
Joined: Fri Dec 11, 2009 1:09 pm

Re: Insurance Plan Report - Current

Postby 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: 257
Joined: Fri Dec 11, 2009 1:09 pm

Re: Insurance Plan Report - Current

Postby 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;


rhaber123
 
Posts: 257
Joined: Fri Dec 11, 2009 1:09 pm


Return to Main Forum

Who is online

Users browsing this forum: No registered users and 4 guests

cron