Add Email To Custom Query?

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
joshuab
Posts: 41
Joined: Fri Jul 03, 2009 2:23 am

Add Email To Custom Query?

Post by joshuab » Tue Nov 06, 2012 7:25 am

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;

joshuab
Posts: 41
Joined: Fri Jul 03, 2009 2:23 am

Re: Add Email To Custom Query?

Post by joshuab » Tue Nov 06, 2012 9:04 am

Disregard - added patient.email after SELECT.

Post Reply