Count of Patients by Provider with and without Medicaid

I need to know by provider how many active patients they have and how many have Medicaid coverage.

This query shows the combined use a an IF expression that evaluates based on a subquery that returns a count. In this case, if there is any Medicaid coverage (carriers.coverage = ‘D’) then the expression returns “Yes”, otherwise (no ‘caid coverage) it returns “No”.

SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
(IF (SELECT COUNT(*)
    FROM sos.ptpayors s1 JOIN sos.payors s2 ON s1.payornum = s2.payornum JOIN sos.carriers s3 ON s2.payornum = s3.payornum
    WHERE s1.ptnum = a.ptnum AND s3.coverage = 'D') > 0
THEN 'YES'
ELSE 'NO'
END IF)
AS "Medicaid",
COUNT(DISTINCT a.ptnum) AS "Pt Count"
FROM
sos.rv_charges a
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider", "Medicaid"
ORDER BY
"Provider", "Medicaid"

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.