For query #21 at http://opendentalsoft.com:1942/ODQueryL ... yList.aspx (see also below), how can I also have it return the email of the patient?
(Goal is to find active patients with treatment plan remaining for the year to send email notice)
Thank you!
/*21*/ SELECT patient.PatNum,tempannualmax.AnnualMax AS $AnnualMax,tempused.AmtUsed AS $AmountUsed,
(CASE WHEN ISNULL(tempused.AmtUsed) THEN (tempannualmax.AnnualMax) ELSE (tempannualmax.AnnualMax-tempused.AmtUsed) END) AS $AmtRemaining,
SUM(pl.ProcFee) AS $TreatPlanned
FROM patient
INNER JOIN patplan ON patient.PatNum=patplan.PatNum
INNER JOIN inssub ib ON ib.InsSubNum=patplan.InsSubNum
INNER JOIN
(SELECT benefit.PlanNum, benefit.MonetaryAmt AS AnnualMax
FROM benefit
WHERE benefit.BenefitType = 5 /* limitation */
AND benefit.TimePeriod = 2 /* calendar year */
AND benefit.CovCatNum=0 /*Annual Max*/
AND benefit.MonetaryAmt > 0
AND benefit.CodeNum=0 /*Not a limitation for a specific code*/) tempannualmax ON tempannualmax.PlanNum=ib.PlanNum
LEFT JOIN
(SELECT patplan.PatPlanNum,
SUM(IFNULL(claimproc.InsPayAmt,0)) AS AmtUsed
FROM claimproc
LEFT JOIN inssub ib ON claimproc.PlanNum=ib.PlanNum
LEFT JOIN patplan ON patplan.PatNum = claimproc.PatNum
AND patplan.InsSubNum=ib.InsSubNum
WHERE claimproc.Status IN (1, 3, 4)
AND claimproc.ProcDate BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND MAKEDATE(YEAR(CURDATE())+1, 1) /*current calendar year*/
GROUP BY patplan.PatPlanNum) tempused ON tempused.PatPlanNum=patplan.PatPlanNum
INNER JOIN procedurelog pl ON pl.PatNum=patient.PatNum AND pl.ProcStatus=1 /*treatment planned*/ AND pl.ProcFee>0
WHERE (CASE WHEN ISNULL(tempused.AmtUsed) THEN (tempannualmax.AnnualMax) ELSE (tempannualmax.AnnualMax-tempused.AmtUsed) END)>0
AND PatStatus=0
GROUP BY patplan.PatPlanNum
ORDER BY $TreatPlanned DESC;
Add Email To Custom Query?
Re: Add Email To Custom Query?
Disregard - added patient.email after SELECT.