Treatment finder query excluding pts scheduled with dentist

[notsosuperman1]

I am trying to run a query to find patients with treatment planned procedures (by certain providers) newer than one year old who don't have appointments scheduled with a dentist. With the built in treatment finder report I can almost get there. I can run a report of patients with recent treatment plans filtered by provider, or I can run that report and see only the ones who DON'T have an appointment. However, I can't drill down by whether the upcoming appointment is with the hygienist or with a dentist.

Basically, I want to find all patients with work to do who aren't scheduled for it. I couldn't find anything that matched this in the query database, and I suppose if I stare at the treatment finder query below long enough I may be able to hack it together, but I thought I'd see if anyone here could make a quick modification to allow for filtering based on upcoming appointment providers. Thanks!

Here is the treatment finder code:
/*1111 Treatment Finder report. Like internal.*/
SET @IncludePatsNoIns='NO';/*Enter either 'YES' or 'NO'*/
SET @IncludePatsWithAppts='NO';/*Enter either 'YES' or 'NO'*/
SET @AmountRemaining='';
SET @TPDateSince='';/*Leave blank as '' to see for all time*/
SET @MonthStart=0;/*Enter 0 for Calendar year, 1 for January, 2 for February, etc.*/
SET @Provs=''; /*Enter provider abbreviations separated by a pipe ('|' without the quotes). To see all leave blank as '' */
SET @BillingTypes=''; /*Enter billing types separated by a pipe ('|' without the quotes). To see all leave blank as '' */
SET @CodeStart='', @CodeEnd='';/*Ex: D1000-D2000*/
/*This report is exactly like the internal treatment finder report with all the same options. It has additional columns for address information.*/
/*Query code written/modified: 01/29/2016*/
SET @RenewDate=(CASE WHEN @MonthStart=0 THEN CONCAT(YEAR(CURDATE()),'-01-01')
WHEN @MonthStart<=MONTH(CURDATE()) THEN CONCAT(YEAR(CURDATE()),'-',LPAD(@MonthStart,2,'0'),'-01')
ELSE CONCAT(YEAR(CURDATE())-1,'-',LPAD(@MonthStart,2,'0'),'-01') END);
SET @Provs=(CASE WHEN @Provs="" THEN "^" ELSE CONCAT('^',REPLACE(@Provs,"|","$|^"),"$") END);
SET @BillingTypes=(CASE WHEN @BillingTypes="" THEN "^" ELSE CONCAT('^',REPLACE(@BillingTypes,"|","$|^"),"$") END);
SELECT patient.PatNum AS 'PatNum ', CONCAT(patient.LName,", ",patient.FName) AS 'Patient',
(CASE patient.PreferRecallMethod WHEN 0 THEN 'None'
WHEN 1 THEN 'DoNotCall'
WHEN 2 THEN 'HmPhone'
WHEN 3 THEN 'WkPhone'
WHEN 4 THEN 'WirelessPh'
WHEN 5 THEN 'Email'
WHEN 6 THEN 'SeeNotes'
WHEN 7 THEN 'Mail'
WHEN 8 THEN 'TextMessage' END) AS 'PreferRecallMethod',
patient.Email, patient.HmPhone,
patient.WirelessPhone, patient.WkPhone, patient.Address,
patient.Address2, patient.City, patient.State, patient.Zip,
patient.PriProv, patient.BillingType,
tblannualmax.AnnualMax "$AnnualMax",
tblused.AmtUsed "$AmountUsed",
tblpending.PendingAmt "$AmountPending",
tblannualmax.AnnualMax-IFNULL(tblused.AmtUsed,0)-IFNULL(tblpending.PendingAmt,0) "$AmtRemaining",
tblplanned.AmtPlanned "$TreatmentPlan", carrier.CarrierName
FROM patient
LEFT JOIN (
SELECT PatNum, SUM(ProcFee) AS AmtPlanned
FROM procedurelog
LEFT JOIN procedurecode ON procedurecode.CodeNum = procedurelog.CodeNum
WHERE ProcStatus=1 /*treatment planned*/
AND IF(@CodeStart='',TRUE,procedurecode.ProcCode>=@CodeStart)
AND IF(@CodeEnd='',TRUE,procedurecode.ProcCode<=@CodeEnd)
AND IF(@TPDateSince='',TRUE,procedurelog.ProcDate>@TPDateSince)
GROUP BY PatNum
) tblplanned ON tblplanned.PatNum=patient.PatNum
LEFT JOIN patplan ON patient.PatNum=patplan.PatNum
LEFT JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum
LEFT JOIN (
SELECT patplan.PatPlanNum,
SUM(IFNULL(claimproc.InsPayAmt,0)) AS AmtUsed
FROM claimproc
LEFT JOIN patplan ON patplan.PatNum=claimproc.PatNum
AND patplan.InsSubNum=claimproc.InsSubNum
WHERE claimproc.Status IN (1,3,4) /*Received, Adjustment, Supplemental*/
AND claimproc.ProcDate BETWEEN @RenewDate AND @RenewDate+INTERVAL 1 YEAR
GROUP BY patplan.PatPlanNum
) tblused ON tblused.PatPlanNum=patplan.PatPlanNum
LEFT JOIN (
SELECT patplan.PatPlanNum,
SUM(IFNULL(claimproc.InsPayEst,0)) PendingAmt
FROM claimproc
LEFT JOIN patplan ON patplan.PatNum=claimproc.PatNum
AND patplan.InsSubNum=claimproc.InsSubNum
WHERE claimproc.Status=0 /*NotReceived*/
AND claimproc.InsPayAmt=0
AND claimproc.ProcDate BETWEEN @RenewDate AND @RenewDate+INTERVAL 1 YEAR
GROUP BY patplan.PatPlanNum
)tblpending ON tblpending.PatPlanNum=patplan.PatPlanNum
LEFT JOIN (
SELECT insplan.PlanNum,
(SELECT MAX(MonetaryAmt)/*for oracle in case there's more than one*/
FROM benefit
LEFT JOIN covcat ON benefit.CovCatNum=covcat.CovCatNum
WHERE benefit.PlanNum=insplan.PlanNum
AND (covcat.EbenefitCat=1 /*General*/ OR ISNULL(covcat.EbenefitCat))
AND benefit.BenefitType=5 /* limitation */
AND benefit.MonetaryAmt>0
AND benefit.QuantityQualifier=0
GROUP BY insplan.PlanNum) AS AnnualMax
FROM insplan
) tblannualmax ON tblannualmax.PlanNum=inssub.PlanNum
AND (tblannualmax.AnnualMax IS NOT NULL AND tblannualmax.AnnualMax>0)/*may not be necessary*/
INNER JOIN provider pr ON pr.ProvNum=patient.PriProv
AND pr.Abbr REGEXP @Provs
INNER JOIN definition def ON def.DefNum=patient.BillingType
AND def.ItemName REGEXP @BillingTypes
LEFT JOIN (
SELECT appointment.PatNum FROM appointment
WHERE appointment.AptStatus IN (1,4)
AND DATE(appointment.AptDateTime)>=CURDATE()
GROUP BY appointment.PatNum
)apt ON apt.PatNum=patient.PatNum
WHERE tblplanned.AmtPlanned>0
AND IF(@IncludePatsNoIns="YES",TRUE,patplan.Ordinal=1 AND insplan.MonthRenew=@MonthStart)
AND IF(@IncludePatsWithAppts="YES",TRUE,apt.PatNum IS NULL) /*only patients with appointments if NO */
AND IF(@AmountRemaining>0,tblannualmax.AnnualMax IS NULL OR tblannualmax.AnnualMax-IFNULL(tblused.AmtUsed,0)>CAST(@AmountRemaining AS DECIMAL(10,2)),TRUE)
AND patient.PatStatus=0
ORDER BY tblplanned.AmtPlanned DESC;

[ryanj]

The problem with trying to identify which dentist or hygienist is on the scheduled appointment is that there can potentially be multiple scheduled appointments for the patient in the specified date range. The query can be changed to show each scheduled appointment in the date range along with the providers listed on the appointment, and/or even be allowed to specify which providers may be on those scheduled appointments.
For us to do this we would need a Query Request submitted on our webpage in order for our engineers to determine if this type of modification would a free modification or if there would be a cost to modify the query they will provide a quote to modify the query. Query writing is quoted at $90/hr, with no obligation to move forward with the request if you don't want to.
Please submit a query request form at this link, and we will work on this for you.
Query Request link:
- http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx