Category: Queries – Accounting

Is there a report or query that we can run that will give us a list of the active patients with a zero balance and no activity within the last 31 days?

In the following query you can adjust the desired period of inactivity by simply changing “31” in the WHERE clause to some other number of days. If you want to sort by primary provider, just change the ORDER BY clause as in the second version below.

(For a related query that returns more information, see http://www.sosoft.com/queries/2014/07/27/716/)

SELECT
  "Name/ID",
  lfeedate AS "Last Service",
  priprvcode AS "Primary Provider"
FROM
  sos.rv_patients a
  JOIN sos.PatientBalance b ON a.ptnum = b.ptnum
WHERE
  flag = 0
  AND lfeedate < ( TODAY() - 31 )
  AND b.ptbalance = 0
  AND a.dischargedate IS NULL
ORDER BY "name/id"

 

I need a list showing all patients that are in collections.

Actually, patients are not in collections in SOS, payors are. The following query produces a list of payors that have a balance in collections, along with their associated patient accounts. The first query sorts in payor order, with payor name in the first column. The second query is the same results, but the patient column is first, and the list is sorted in that order.

SELECT
  a.payorname +', '+a.firstname AS "Payor",
  a.collectdate AS "To Collections",
  b.balance,
  c.lastname+', '+c.firstname+' / '+id AS "Account"
FROM
  sos.payors a
  JOIN sos.ptpayors b ON a.payornum = b.payornum
  JOIN sos.patients c ON b.ptnum = c.ptnum
WHERE
  b.balance > 0
  AND a.collectdate IS NOT NULL
ORDER BY "Payor"

 

Same as the above, but in Patient Account order:

 

SELECT
  c.lastname+', '+c.firstname+' / '+id AS "Account",
  a.collectdate AS "To Collections",
  b.balance,
  a.payorname +', '+a.firstname AS "Payor"
FROM
  sos.payors a
  JOIN sos.ptpayors b ON a.payornum = b.payornum
  JOIN sos.patients c ON b.ptnum = c.ptnum
WHERE
  b.balance > 0
  AND a.collectdate IS NOT NULL
ORDER BY "Account"

 

Another example, this one with primary provider, payor address and phone numbers, and last date of service:

 

SELECT
  c.lastname+', '+c.firstname AS "AccountName",
  c.id AS "AccountNumber",
  c.socsec AS "PatientSSNum",
  d.provlname +' '+ d.provfname AS "PriProvider",
  a.payorname +' '+a.firstname AS "Payor",
  a.Addr1,
  a.Addr2,
  a.City,
  a.State,
  a.zip,
  a.Phone1Desc,
  a.Phone1Area +'-'+ a.Phone1 AS "Phone_1",
  a.Phone1Ext,
  a.Phone2Desc,
  a.Phone2Area +'-'+ a.Phone2 AS "Phone_2",
  a.Phone2Ext,
  a.Phone3Desc,
  a.Phone3Area +'-'+ a.Phone3 AS "Phone_3",
  a.Phone3Ext,
  lastchargedate(c.ptnum) AS "Last DOS",
  b.balance
FROM
  sos.payors a
  JOIN sos.ptpayors b ON a.payornum = b.payornum
  JOIN sos.patients c ON b.ptnum = c.ptnum
  LEFT OUTER JOIN sos.providers d ON c.providernum = d.providernum
WHERE
  b.balance > 0
  AND a.collectdate IS NOT NULL
ORDER BY 
  "AccountName"