Open Dental Forum

Hello! I'm sure this has been asked before, but I couldn't narrow down my search well enough to find the answer. I need to edit the following query to return ALL fee schedules, not just one. I tried using %% and that didn't work. I tried leaving it blank which returned an error.

SET @ExcludeProcCodes='%%';
SET @StartDate='2024-05-29', @EndDate='2024-06-29';
SET @ProvTreat='%%';
SET @FeeSchedDescr='Connection 11.1.23';
SELECT claim.PatNum,DateService,prov.Abbr AS 'ProcTreat',pc.ProcCode,cp.InsPayEst AS '$InsPayEst_',(pl.ProcFee*(pl.BaseUnits+pl.UnitQty))-cp.InsPayEst-cp.WriteOffEst AS '$EstPatPortion_',f.Amount AS '$UCR Fee_',
cp.InsPayAmt AS '$InsPayAmt_',car.CarrierName
FROM claim
INNER JOIN claimproc cp ON claim.ClaimNum=cp.ClaimNum
INNER JOIN insplan ip ON ip.PlanNum=claim.PlanNum
INNER JOIN carrier car ON car.CarrierNum= ip.CarrierNum
INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider prov ON prov.ProvNum=claim.ProvTreat AND prov.Abbr LIKE @ProvTreat
INNER JOIN fee f ON pc.CodeNum=f.CodeNum
INNER JOIN feesched fs ON f.FeeSched=fs.FeeSchedNum AND fs.Description=@FeeSchedDescr
WHERE DateService BETWEEN @StartDate AND @EndDate
AND pc.ProcCode NOT REGEXP @ExcludeProcCodes

Hi Casey,

To modify your query to return results for all fee schedules instead of a specific one do these two things:
1. Remove the @FeeSchedDescr variable setting since it's used to filter by a specific fee schedule.
2. Remove the condition in the 'INNER JOIN' clause that filters based on 'fs.Descrption = @FeeSchedDescr'.

Modified version:

SET @ExcludeProcCodes='%%';
SET @StartDate='2024-05-29', @EndDate='2024-06-29';
SET @ProvTreat='%%';

SELECT
claim.PatNum,
DateService,
prov.Abbr AS 'ProcTreat',
pc.ProcCode,
cp.InsPayEst AS '$InsPayEst_',
(pl.ProcFee*(pl.BaseUnits+pl.UnitQty))-cp.InsPayEst-cp.WriteOffEst AS '$EstPatPortion_',
f.Amount AS '$UCR Fee_',
cp.InsPayAmt AS '$InsPayAmt_',
car.CarrierName
FROM
claim
INNER JOIN claimproc cp ON claim.ClaimNum = cp.ClaimNum
INNER JOIN insplan ip ON ip.PlanNum = claim.PlanNum
INNER JOIN carrier car ON car.CarrierNum = ip.CarrierNum
INNER JOIN procedurelog pl ON cp.ProcNum = pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
INNER JOIN provider prov ON prov.ProvNum = claim.ProvTreat AND prov.Abbr LIKE @ProvTreat
INNER JOIN fee f ON pc.CodeNum = f.CodeNum
INNER JOIN feesched fs ON f.FeeSched = fs.FeeSchedNum -- Removed specific description filter
WHERE
DateService BETWEEN @StartDate AND @EndDate
AND pc.ProcCode NOT REGEXP @ExcludeProcCodes;