Open Dental Forum

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.LName, 
p.FName,
p.HmPhone,
p.WkPhone,
p.WirelessPhone,
p.Email,
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;