Category: Queries – Patients

Mailing Labels for Active Patients with Zero Balance

Posted on | by SOS Blog | 2 Comments on Mailing Labels for Active Patients with Zero Balance

I would like to send a flyer to all patients who have no balance and who have been seen within the last six months.

The following query specifies only that the patient has been seen for some sort of service in the last 180 days and that the current balance is zero or less.

Check the appropriate How-To document in the blue bar at the top of the page for instructions on saving the results to HTML format that you can open and save with Excel. You can then use the Mail-Merge feature in MS Word to create your labels using this data. See Mail Merge Labels in Word Using Query Results.

SELECT
   (a.firstname + ' '+a.lastname) AS "name",
   a.addr1 AS "addr1",
   a.addr2 AS "addr2",
   (a.city+', '+a.state+' '+a.zip) AS "addr3"
FROM
   sos.rv_patients a
   JOIN sos.pt_noninsbalance  b ON a.ptnum = b.ptnum
WHERE
   b.ptbalance <= 0
   AND lfeedate > (TODAY()-180)
; OUTPUT TO c:\sos\labels.html FORMAT HTML

 

Patients with Primary Provider and Primary Dx, Sorted by Provider Code

Posted on | by SOS Blog | Leave a Comment on Patients with Primary Provider and Primary Dx, Sorted by Provider Code

This query demonstrates the use of IF expressions to display “None” rather than “NULL” wherever there is no primary provider or dx.

SELECT 
  Patients.LastName, 
  Patients.FirstName, 
  Patients.ID, 
  (IF Patients.ProviderNum IS NULL 
  THEN 'None' 
  ELSE (Providers.ProvCode +':'+ Providers.ProvLName +', ' + Providers.ProvFName) 
  END IF) AS "Provider", 

  (IF ptcsudx.DxCode1 IS NULL THEN 'None' 
  ELSE ptcsudx.DxCode1 
  END IF) AS "PrimaryDx" 

FROM 
  sos.Patients 
  LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum 
  LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum 
  LEFT OUTER JOIN sos.ptcsudx ON PtCSU.ptcsunum =ptcsudx.ptcsunum 
WHERE 
  Patients.LicNum = 101 
  AND Patients.Flag = 0 
  AND Patients.DischargeDate IS NULL 
  AND PtCSU.TypeFlag = 'D' 
ORDER BY 
  Providers.ProvCode,Patients.LastName,Patients.FirstName,Patients.id 
;
OUTPUT TO c:\sos\provptdxlist.html FORMAT HTML

Patients by Insurance Carrier and Group Number

Posted on | by SOS Blog | Leave a Comment on Patients by Insurance Carrier and Group Number

Is there a patient report that lists all patients by insurance group number?

 

SELECT 
  a.payorname,a.insgroup,b.lastname,b.firstname,b.id
FROM 
  sos.rv_policies a JOIN sos.patients b ON a.ptnum = b.ptnum
WHERE 
  today() BETWEEN COALESCE(a.active,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')
ORDER BY 
  payorname, insgroup, b.lastname, b.firstname, b.id

Expiring Authorizations by Primary Provider

Posted on | by SOS Blog | Leave a Comment on 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"

List Patients with No Primary Diagnosis by Primary Provider

Posted on | by SOS Blog | Leave a Comment on List Patients with No Primary Diagnosis by Primary Provider

This is a relatively easy query that can be used to generate a list of patients for which no diagnosis has
been entered. Technically, the selection is for a missing Dx1, so if for some reason there is no Dx1, but
there is a Dx entered in Dx2, 3, or 4 the name will still appear on the list.

Note that we use LEFT OUTER JOINs between the Patients table and the Providers and PtCSU tables
so that if there is no primary provider entered, or the default claim setup is missing for some reason, we
will still see the patient name in the result set. Also notice the OR condition in the WHERE clause. Here
we are looking for a missing Dx link (null) or a link number of zero (which is what it should be if no Dx
has been selected). It is ALWAYS a good idea to put your OR expressions within parentheses so that the
query parser does not make it’s own decision about this matter. Here it would not really matter because
there is no ambiguity, but it is still a good habit and one that will save you much confusion when your
conditions are more complex.

SELECT
   Providers.ProvCode,
   Providers.ProvLName, 
   Providers.ProvFName,
   Patients.LastName, 
   Patients.FirstName, 
   Patients.ID
FROM
   sos.Patients
   LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
   LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum
WHERE
   Patients.LicNum = 101 
   AND Patients.Flag = 0 
   AND PtCSU.TypeFlag = 'D' 
   AND (PtCSU.Dx1 IS NULL OR PtCSU.Dx1 = 0)
ORDER BY
   Providers.ProvCode,
   Patients.LastName, 
   Patients.FirstName,Patients.ID