I know that the procedurelog table has the procfee and one could just total that BUT the claimproc table has adjusted amounts based on expected payment form insurance. If I run the below I am able to see a table that shows me both just the total procfee AND the adjusted procfees totals. The problem is sometimes one matches ODs report scheduled, sometimes the other, and sometimes neither. Would you be able to explain what I am not accounting for that would allow me to calculate the same scheduled $ as that report? Thank you for your time.
My trial test query:
Code: Select all
SET @today='2022-05-19',@lastThisMonth='2022-05-31';
SELECT pl.ProcDate,
pl.ClinicNum,
SUM(pl.ProcFee),
SUM(X.FutureInsurance),
SUM(IFNULL(X.FutureInsurance,pl.ProcFee)) AS 'ACTUAL'
FROM procedurelog pl
LEFT JOIN (SELECT ProcNum, InsPayEst AS 'FutureInsurance'
FROM claimproc
WHERE ProcDate BETWEEN @today AND DATE_ADD(@lastThisMonth, INTERVAL 1 DAY)) X ON pl.ProcNum=X.ProcNum
WHERE AptNum IN (SELECT AptNum FROM appointment ap WHERE AptDateTime BETWEEN @today AND DATE_ADD(@lastThisMonth, INTERVAL 1 DAY))
AND ProcStatus != 2
AND ClinicNum = 5
GROUP BY ProcDate;