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 sos.services s ON c.servicenum = s.servicenum
  j.trandate BETWEEN '2000-01-01' AND '2012-12-31'
  AND s.insbillable = 1

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.