Treatment finder query excluding pts scheduled with dentist
Posted: Tue Jan 10, 2017 5:33 am
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;
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;