Category: Management

Does anyone out there have a query we can use to get data regarding the number of visits a client makes to a provider? We would like to follow how often people come to see our providers. I imagine the query would track by provider, by client, intake date, and subsequent treatment visits.

Take a look at the following, relatively complex query, which delivers the average number of non-zero$ service entries for each provider, plus the itemized list of patients seen by the provider, with the number of services rendered for each patient. The WHERE clauses allow you to specify the selection is for when the patient’s intake date or first non-zero service falls with a specified date range. You must specify the date range in TWO places, and the provider codes to analyze in TWO places. The reason is that there are two queries that are UNIONed into a single result set. One does the details and the other does the average. They won’t match up unless the conditions are set identically for both. This guy also uses DATE, TODAY, and COALESCE functions and the MIN and COUNT aggregate functions, plus an embedded, correlated subquery to determine the date of the first service for each patient. It is definitely not a place to start if you wanted to learn basic SQL, but it has lots of interesting features if you are ready to try your hand at more advanced stuff.

Anyway, you don’t have to understand it to use it! Just paste it into your DBISQL utility, modify the selection criteria, and let ‘er rip.

Note that there is a conceptual gotcha. By including patients who are still in treatment, your results may be invalid. Let’s say that you have a new provider who has only been seeing patients for two months. Assuming weekly visits, her average visits per patient will come up as 8 or fewer. The results are going to be skewed by the number of patients who are still being seen.

I think it is more meaningful to only include patients who have been discharged, so that you are looking at a completed episode of care for each patient. To do so, you would add:

AND (p.flag = 0 OR p.dischargedate IS NOT NULL)

to the two WHERE clauses. That will include only patients in the Inactive list or who have a date in the Discharge Date field. I have done that below, but the lines won’t execute unless you remove the two slashes at the beginning of the additional condition in the WHERE clause of both of the main queries.

SELECT
  v.provcode,
  p.lastname,p.firstname,p.id,
  DATE(COALESCE(intakedate,(SELECT MIN(trandate)
                            FROM sos.journal
                            WHERE ptnum = p.ptnum
                            AND trantype='S' AND amount > 0)
       ,'1900-01-01')) AS "Initial",
  COUNT(DISTINCT j.jnum) AS "Visits"
FROM
  sos.patients p
  JOIN sos.journal j ON p.ptnum = j.ptnum
  JOIN sos.jcharges c ON j.jnum = c.jnum
  JOIN sos.providers v ON c.providernum = v.providernum
WHERE
  "Initial" BETWEEN '1995-01-01' AND TODAY()
  AND v.provcode IN ('A','B','C')
  //AND (p.flag = 0 OR p.dischargedate IS NOT NULL)
GROUP BY
  v.provcode,p.lastname,p.firstname,p.id,"Initial"
UNION
SELECT
  v.provcode,
  '--AVERAGE','NUMBER OF','VISITS',
   NULL,
  COUNT(DISTINCT j.jnum)/COUNT(DISTINCT j.ptnum) AS "Avg Vists"
FROM
  sos.patients p
  JOIN sos.journal j ON p.ptnum = j.ptnum
  JOIN sos.jcharges c ON j.jnum = c.jnum
  JOIN sos.providers v ON c.providernum = v.providernum
WHERE
  DATE(COALESCE(intakedate,(SELECT MIN(trandate)
                            FROM sos.journal
                            WHERE ptnum = p.ptnum
                            AND trantype='S'
                            AND amount > 0)
       ,'1900-01-01')) BETWEEN '1995-01-01' AND TODAY()
  AND v.provcode IN ('A','B','C')
  //AND (p.flag = 0 OR p.dischargedate IS NOT NULL)
GROUP BY
  v.provcode
ORDER BY
  1,2,3,4