Session Distribution By Provider
Back to Query Directory
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
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
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
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
CategoryQueries
