Category: Queries – Providers

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"

Let’s take a look at this request from a detailed and summary angle.

SELECT
   Lastname,
   firstname,
   id,
   COALESCE(provcode,'None') AS "Provider",
   Intakedate,dischargedate,
   DateDiff(day,intakedate,dischargedate) AS "Days"
FROM 
   sos.patients a
   JOIN sos.providers b ON a.providernum=b.providernum
WHERE
   intakedate IS NOT NULL
   and dischargedate IS NOT NULL

....will give you a list of patients, their intake and discharge dates, and the number of days between those
dates.

Extending that to give us a summary, with provider, average length of stay, and the number of patients
from which the statistics are derived:

SELECT
   COALESCE(provcode,'None') AS "Provider",
   COUNT(*) AS "N",
   AVG(DateDiff(day,intakedate,dischargedate)) AS "Days"
FROM 
   sos.patients a
   JOIN sos.providers b ON a.providernum=b.providernum
WHERE
   intakedate IS NOT NULL
   and dischargedate IS NOT NULL
GROUP BY 
   "Provider"
ORDER BY 
   "Provider"