Session Distribution By Provider

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