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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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
   ptauths a
   JOIN ptpolicies b ON a.ptpolnum = b.ptpolnum
   JOIN ptpayors c ON b.ptpayornum = c.ptpayornum
   JOIN payors d ON c.payornum = d.payornum
   JOIN patients e ON a.ptnum = e.ptnum
   LEFT OUTER JOIN providers f ON e.providernum = f.providernum
   LEFT OUTER JOIN 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"