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:
SELECT
YEAR(j.trandate) AS yr,MONTH(j.trandate) AS mon,
p.provcode,
count(distinct j.ptnum) AS pts,
count(distinct j.jnum) AS visits,
visits/pts AS average
FROM
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
WHERE
j.trandate BETWEEN '2000-01-01' AND '2012-12-31'
AND s.insbillable = 1
GROUP BY ROLLUP
(yr,mon,provcode)
ORDER BY
yr,mon,provcode
