I don’t know about other people but, I could really use query to provide a simple table of PA’s that are
nearly exhausted. The MC auth report is just too big.
Criteria is simply PAs < given_number and Exp Date between given_dates
Provider | Patient | PAs Remaining | Exp.Date
sorted by provider, patient
I added a couple of refinements, including a column for the insurer’s name, and both the primary
provider and authorized provider. You can change the ORDER BY to reflect the one you want. I also
tuned up the conditions in the WHERE clause to eliminate inactive/discharged patients and inactive
authorizations, and included the expiration date in the selection conditions (third to last line). The
“TODAY() + 14” in this example means that auths with expiration dates within the next 14 days will be
selected. Obviously, the “3” in the same line selects auths with less than 3 visits remaining.
SELECT f.provcode AS "PrimaryProvider", g.provcode AS "AuthorizedProvider", (e.lastname + ', ' + e.firstname + ' / ' + e.id) AS "Patient", d.payorname AS "Insurer", (a.maxvisits - a.usedvisits) AS "VisitsLeft", a.enddate AS "ExpDate" FROM sos.ptauths a JOIN sos.ptpolicies b ON a.ptpolnum = b.ptpolnum JOIN sos.ptpayors c ON b.ptpayornum = c.ptpayornum JOIN sos.payors d ON c.payornum = d.payornum JOIN sos.patients e ON a.ptnum = e.ptnum LEFT OUTER JOIN sos.providers f ON e.providernum = f.providernum LEFT OUTER JOIN sos.providers g ON a.providernum = g.providernum WHERE a.status = 'A' //active auths only AND e.flag = 0 //active patients only AND e.dischargedate is null // no discharge date entered AND ("VisitsLeft" < 3 OR "ExpDate" < TODAY() + 14) ORDER BY "PrimaryProvider", "Patient"