Scheduled KPI in Monthly Production and Income report

This forum is for programmers who have questions about the source code.
Post Reply
baoluan1
Posts: 13
Joined: Wed Jan 19, 2022 8:37 am

Scheduled KPI in Monthly Production and Income report

Post by baoluan1 » Thu May 19, 2022 9:37 am

On the Monthly Production and Income OD report if you run it mid month you have scheduled $ for future days. I have been trying to replicate this one KPI through the API but cant seem to match it.

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;

Post Reply