need to add address info to a query

[mzrdmd]

I went to the queries page and selected #252, which is "Treatment planned work totalled by patient with annual ins max, ins used and name of carrier", and I ran the query and exported it to Excel. I now want to use it as a database for a mail merge, but there are no addresses with it !

How can I add the address into the query so that I can do a mail merge ?

Thanks,

Mike

[atd]

Database documentation can be found here:
http://opendental.com/OpenDentalDocumentation67.xml

You'd need to add the following fields to your select statement
patient.Address
patient.Address2
patient.City
patient.State
patient.Zip

[mzrdmd]

Awesome ! Thank you !

This kind of interaction is one of the reasons why I LOVE OD and this forum !

Mike

[mzrdmd]

I don't know how to use this stuff. Help ?

Thanks,

Mike

[Jorgebon]

You have to add the address fields to the query as follows:

/*Treatment planned work totalled by patient with annual ins max, ins used and name of carrier*/
DROP TABLE IF EXISTS tempused;
DROP TABLE IF EXISTS tempplanned;
DROP TABLE IF EXISTS tempannualmax;
CREATE TABLE tempused(
PatPlanNum mediumint unsigned NOT NULL,
AmtUsed double NOT NULL,
PRIMARY KEY (PatPlanNum));
CREATE TABLE tempplanned(
PatNum mediumint unsigned NOT NULL,
AmtPlanned double NOT NULL,
PRIMARY KEY (PatNum));
CREATE TABLE tempannualmax(
PlanNum mediumint unsigned NOT NULL,
AnnualMax double NOT NULL,
PRIMARY KEY (PlanNum));
INSERT INTO tempused
SELECT patplan.PatPlanNum,
SUM(IFNULL(claimproc.InsPayAmt,0))
FROM claimproc
LEFT JOIN patplan ON patplan.PatNum = claimproc.PatNum
AND patplan.PlanNum = claimproc.PlanNum
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;
INSERT INTO tempplanned
SELECT PatNum, SUM(ProcFee)
FROM procedurelog
WHERE ProcStatus = 1 /*treatment planned*/
GROUP BY PatNum;
INSERT INTO tempannualmax
SELECT benefit.PlanNum, benefit.MonetaryAmt
FROM benefit, covcat
WHERE covcat.CovCatNum = benefit.CovCatNum
AND benefit.BenefitType = 5 /* limitation */
AND benefit.TimePeriod = 2 /* calendar year */
AND covcat.EbenefitCat=1
AND benefit.MonetaryAmt <> 0
GROUP BY benefit.PlanNum
ORDER BY benefit.PlanNum;

SELECT patient.LName, patient.FName, patient.address, patient.address2, patient.city, patient.state, patient.zip,
tempannualmax.AnnualMax $AnnualMax,
tempused.AmtUsed $AmountUsed,
tempannualmax.AnnualMax-IFNULL(tempused.AmtUsed,0) $AmtRemaining,
tempplanned.AmtPlanned $TreatmentPlan,
c.carriername
FROM patient
LEFT JOIN tempplanned ON tempplanned.PatNum=patient.PatNum
LEFT JOIN patplan ON patient.PatNum=patplan.PatNum
LEFT JOIN insplan ip ON ip.PlanNum=patplan.PlanNum
LEFT JOIN carrier c ON c.CarrierNum=ip.CarrierNum
LEFT JOIN tempused ON tempused.PatPlanNum=patplan.PatPlanNum
LEFT JOIN tempannualmax ON tempannualmax.PlanNum=patplan.PlanNum
AND tempannualmax.AnnualMax>0
/*AND tempannualmax.AnnualMax-tempused.AmtUsed>0*/
WHERE tempplanned.AmtPlanned>0 AND AnnualMax > 0
ORDER BY tempplanned.AmtPlanned DESC;
DROP TABLE tempused;
DROP TABLE tempplanned;
DROP TABLE tempannualmax;

Jorge Bonilla, DMD