Find Patients with No Recent Activity

I would like to develop one I can use prior to record destruction.  The information I want is: list of client names, last name then first (all active andinactive), date of birth, age (the day of query run), first date seen, last date seen, last transaction, last payment, all providers that rendered service, and patient category (if assigned).  I think this sounds complicated.  Can it be done in one query?  Would first date seen pull from intake date or first transaction or first charge?  Would last date seen pull from last charge, discharged date, or last transaction?

In the query below, “last transaction” is the most recent entry of any kind in the patient’s ledger. If more than one provider rendered services to the patient, the results show a line for each. The “first seen on” and “last seen on” dates are specific to the provider shown on that line, but the “last payment” and “last transaction” are not provider-specific.

This query features a couple of functions that we created here at SOS and added to the library of functions available in SQL Anywhere. AGEINYEARS returns an accurate calculation of age, taking leap years into consideration. LASTCREDITDATE examines the patient’s ledger and returns the date of the last actual payment — that is, it ignores adjustment credits.

The WHERE clause filters the results by data set (licnum), includes both active and inactive patient lists, and returns only those patients with a last transaction date at least 180 days ago. Of course, you can modify each of those values for your specific needs.

Also of interest is that the SELECT list of this query consists mostly of subqueries. Using subqueries in this fashion often results in much better performance, even when the same results can be achieved with additional joins in the main query. As shown here, each subquery must be within a set of parentheses and be followed by “AS <your column name>”.

  a.lastname + ', ' + a.firstname AS "Name", AS "Account ID",
  a.dob AS "DOB",
  sos.AGEINYEARS(a.dob,today()) AS "Age",
  ( SELECT provcode FROM sos.providers WHERE providernum = b.providernum) AS "Provider",
  ( SELECT MIN(trandate) FROM sos.journal jou JOIN sos.jcharges chg 
    WHERE jou.ptnum = a.ptnum AND chg.providernum = b.providernum AND trantype = 'S' AND amount > 0) AS "First Seen On",
  ( SELECT MAX(trandate) FROM sos.journal jou JOIN sos.jcharges chg 
    WHERE jou.ptnum = a.ptnum AND chg.providernum = b.providernum AND trantype = 'S' AND amount > 0) AS "Last Seen On",
  ( SELECT sos.LASTCREDITDATE(a.ptnum)) AS "Last Payment",
  ( SELECT MAX(trandate) FROM sos.journal WHERE ptnum = a.ptnum) AS "Last Transaction",
  ( SELECT categcode FROM sos.ptcategs WHERE ptcategnum = a.ptcategnum) AS "Pt Category"
  sos.patients a
  LEFT OUTER JOIN sos.jcharges b ON a.ptnum = b.ptnum
  a.licnum = 101  //specifies the dataset to be examined
  AND a.flag IN(0,1)  //includes both active (0) and inactive (1) patient lists
  AND "Last Transaction" < (TODAY() - 180)  //show only those whose last transaction was 180 days ago
ORDER BY "Name","Account ID","Provider"

Simple Export of Basic Patient Information

I need to export the following fields in Excel or .csv format:

Last name, first name, address (all fields), primary phone, email (optional), DOB, sex, primary provider, diagnosis, primary insurance carrier, insured ID#


Instead of going directly to CSV, we recommend exporting in HTML format instead. You can do a quick check on the results by just double-clicking the file (in this example, the results are saved in the file name MYEXPORT.HTML in the SOS folder). You can then open the file in Excel by starting Excel and using File > Open, being careful to select the appropriate file type. In Excel 2013 the type is “All Web Pages”. The results will appear with each data element in its own column and a heading at the top of each column. At that point, you can manipulate the data as you like and save as CSV or any other format supported by Excel.


Here is the query, including the OUTPUT line that saves the results as an HTML file. Note that the WHERE clause filters the results to include only patients in dataset 101 (licnum = 101), who appear in the Active Patient List (flag = 0), and who have not been discharged (discharge date IS NULL). You can change or remove any of these filters as needed, or add new ones.

  ,(a.phone1area+'-'+a.phone1) AS phone
  ,b.provlname+', '+b.provfname AS primary_provider
  ,c.payorname AS primary_insurance
  ,c.insdid AS insured_id
  sos.rv_patients a
  JOIN sos.providers b ON a.priprvcode = b.provcode
  JOIN sos.rv_policies c ON a.ptnum = c.ptnum
  JOIN sos.dx d ON a.dx1=d.dxnum
  a.licnum = 101
  AND b.licnum = 101
  AND a.flag = 0
  AND a.dischargedate IS NULL
  AND c.inspos = 1
OUTPUT TO c:\sos\myexport.html FORMAT html

List Secondary Insurance Policies by Patient

I need a list of patients who have secondary insurance, including the secondary policy information (payer name, subscriber number, and group number).

The following solution takes into consideration that we could have a mix of active and inactive policies, with inactive policies above active ones in the list. We therefore have to filter out the policies that are not active on the date of the query, and re-assign corrected position numbers to the remaining policies. We can filter out the inactive ones using a simple WHERE condition, but renumber the rest requires use of an advanced RANK expression, including an optional PARTITION parameter. All that is included as a subquery in an outer container query that functions as an ad-hoc view by assigning the subquery an Alias (in this case X). That ad-hoc view is linked to the other normal view (rv_policies) and the patient table in the FROM clause. A condition in the WHERE of the outer query filters out the primary policy, leaving only lower order coverage.

 b.ptnum, b.lastname,b.firstname,, a.payorname,a.insgroup,x.CurrentInsPos
  sos.rv_policies a 
  JOIN sos.patients b ON a.ptnum = b.ptnum 
  JOIN (SELECT a.ptnum, a.ptpolnum, RANK() OVER (PARTITION BY a.ptnum ORDER BY a.ptnum,a.inspos) AS "CurrentInsPos"
        FROM sos.ptpolicies a 
        WHERE today() BETWEEN COALESCE(,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')) X ON a.ptpolnum = x.ptpolnum
  b.flag = 0
  AND b.licnum = 101
  AND x.currentinspos > 1
  b.lastname, b.firstname,, x.currentinspos

The query above excludes the primary insurance. The version below includes all active policies in the result set, but only for patients who have two or more currently active policies.

  b.ptnum, b.lastname,b.firstname,, 
  RANK() OVER (PARTITION BY a.ptnum ORDER BY a.ptnum,a.inspos) AS "CurrentInsPos",
  sos.rv_policies a 
  JOIN sos.patients b ON a.ptnum = b.ptnum 
  JOIN (SELECT ptnum,count(*) as "Active Policies"
        FROM sos.ptpolicies a 
        WHERE today() BETWEEN COALESCE(,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')
        GROUP BY ptnum
        -- HAVING "Active Policies" > 1 
       ) AS X ON a.ptnum = x.ptnum
  b.flag = 0
  AND b.licnum = 101
  AND x."Active Policies" > 1

Average Number of Visits by Year, Month, and Provider

 I’m looking for a query that tells me the AVERAGE number of treatment sessions per patient (as defined by a service code whose “include on claims” box is checked in SERVICES under LOOKUPS) for a date range.  I’d prefer that the query not list every patient but an average of each provider’s patients.
SO, given our 75 providers, what is each providers average amount of sessions conducted per patient over a 4 week period. If a provider saw every patient once a week, their average would be 4.  If another provider saw every patient every two weeks, their average would be 2.

Interesting question and (mostly) answered by a simple query. Note that there is built-in error due to mid-month intakes, but if we assume that variable is relatively stable, then this should give you the trend you want.

The data is output by year, month, and provider code. You can set the range to be examined by altering the date range in the WHERE clause. Instead of a plain GROUP BY, this query uses GROUP BY ROLLUP to provide  subtotals. Remember that where you see NULL in the output, read it as “All”. The ROLLUP will then give you averages for the entire period, each year, and each month, as well as by provider and across providers:


  YEAR(j.trandate) AS yr,MONTH(j.trandate) AS mon,
  count(distinct j.ptnum) AS pts,
  count(distinct j.jnum) AS visits,
  visits/pts AS average
  sos.journal j 
  JOIN sos.jcharges c ON j.jnum = c.jnum
  JOIN sos.providers p ON c.providernum = p.providernum
  JOIN s ON c.servicenum = s.servicenum
  j.trandate BETWEEN '2000-01-01' AND '2012-12-31'
  AND s.insbillable = 1

Account Aging Balance Detail on Specified Date

I want to see a detailed view of the balance for a particular account on a previous date. The aging report gives me the balance on that date, but I want to see the detail for each item that contributes to the aggregate balance figure.

I think this may give you what you need. You will notice on line 15 (just before the WHERE):


 If you remove the dashes at the beginning of that line, it will add many more columns in the output. You can pick and choose from those to add above that line, then put the dashes back in to hide the extra columns. In the WHERE, just change the ID to the account you want to check out.

This query will list only those chargesplits that were still carrying a balance on June 30, 2012. For any other date, change the dates in both line 13, 14, and line 29.

  a.trandate as "service date",
  a.amount as "fee",
  d.payorname as "responsible payor",
  a.chgspldate as "chg split date",
  a.chgsplamt as "chg split amt",
      coalesce(sum(crsplamt),0) from sos.rv_creditsplits 
      cre_date < '2012-07-01'  
      and dateapplied < '2012-07-01' 
      and chgsplnum = a.chgsplnum) as "credits applied",
  a.chgsplamt - "credits applied"  as "chg split balance",
  b.crsplamt as "credit split amt",
  b.credtype as "credit type", 
  b.dateapplied as "date applied"
  sos.rv_charges a 
  left outer join sos.rv_creditsplits b on a.chgsplnum = b.chgsplnum
  join sos.ptpayors c on a.ptpayornum = c.ptpayornum
  join sos.payors d on c.payornum = d.payornum
where = '123456789'
  and "chg split balance" <> 0
  and a.chgspldate < '2012-07-01'
order by 
  "service date","chg split date"