Expiring Authorizations by Primary Provider

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

Output like:

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"