I would like to look at each provider in terms of number of clients who were only seen 1x, 1-3x, 4-6x, and more than 6x. I am looking at trends with
regards to which providers may tend to not keep patients.
This query is a bit more complex. It involves double-level grouping with a subquery and a virtual view, along with IF expressions. The inner query
groups by patient; the outer query collapses the results of the inner query by provider. Adjust the date range in the inner SELECT statement, and note that I have defined “session” as a charge entry with a fee (amount) greater than zero:
SELECT provcode, SUM("1x") AS "SingleSession", SUM("2-3x") AS "Two-Three", SUM("4-6x") AS "Four-Six", SUM("7+") AS "SevenPlus" FROM (SELECT provcode,id, count(DISTINCT jnum) AS SrvDateCount, ((IF SrvDateCount = 1 THEN 1 ELSE 0 ENDIF)) AS "1x", (IF SrvDateCount BETWEEN 2 AND 3 THEN 1 ELSE 0 ENDIF) AS "2-3x", (IF SrvDateCount BETWEEN 4 AND 6 THEN 1 ELSE 0 ENDIF) AS "4-6x", (IF SrvDateCount > 6 THEN 1 ELSE 0 ENDIF) AS "7+" FROM sos.rv_charges WHERE trandate BETWEEN '2001-01-01' AND '2008-03-31' AND amount > 0 GROUP BY (provcode,id)) AS X GROUP BY provcode ORDER BY provcode