Need help to add email to a query

For users or potential users.
Post Reply
babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Need help to add email to a query

Post by babysilvertooth » Wed Sep 21, 2016 1:09 pm

What do I do to get a pull of email for the patients that show up on this query? I want to send an email blast..... Many of these types of queries dont' have the pull of email ID

/*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='2013-10-08';
SELECT p.PatNum AS 'Pat#',
p.LName,
p.FName,
p.HmPhone,
p.WkPhone,
p.WirelessPhone,
DATE_FORMAT(lastseen.LastSeen,'%m/%d/%Y')AS LastSeen,
annualmax.AnnualMax '$AnnualMax_',
used.AmtUsed '$AmountUsed_',
annualmax.AnnualMax-COALESCE(used.AmtUsed,0) '$AmtRemaining_',
planned.AmtPlanned '$TreatmentPlan_',
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
INNER JOIN (
SELECT patient.PatNum,SUM(pl.ProcFee) AS AmtPlanned
FROM patient
INNER JOIN (
SELECT PatNum,ProcFee FROM procedurelog WHERE ProcStatus=1 /*treatment planned*/ AND ProcFee!=0
) pl ON patient.PatNum=pl.PatNum
WHERE patient.PatStatus=0 /* Patient */
GROUP BY patient.PatNum
) planned ON planned.PatNum=p.PatNum AND planned.AmtPlanned>0
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;

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Need help to add email to a query

Post by Tom Zaccaria » Wed Sep 21, 2016 3:06 pm

try adding this line right after p.WirelessPhone,

p.email,

drtmz

drmkpillai
Posts: 58
Joined: Sun Aug 24, 2014 1:43 am

Re: Need help to add email to a query

Post by drmkpillai » Wed Sep 21, 2016 8:45 pm

/*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='2013-10-08';
SELECT p.PatNum AS 'Pat#',
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_',
planned.AmtPlanned '$TreatmentPlan_',
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
INNER JOIN (
SELECT patient.PatNum,SUM(pl.ProcFee) AS AmtPlanned
FROM patient
INNER JOIN (
SELECT PatNum,ProcFee FROM procedurelog WHERE ProcStatus=1 /*treatment planned*/ AND ProcFee!=0
) pl ON patient.PatNum=pl.PatNum
WHERE patient.PatStatus=0 /* Patient */
GROUP BY patient.PatNum
) planned ON planned.PatNum=p.PatNum AND planned.AmtPlanned>0
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;

Post Reply