Count of Patients by Provider with and without Medicaid
Back to Query Directory
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 ptpayors s1 JOIN payors s2 ON s1.payornum = s2.payornum JOIN 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
rv_charges a
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider", "Medicaid"
ORDER BY
"Provider", "Medicaid"
(a.provfname + ' '+a.provlname) AS "Provider",
(IF (SELECT COUNT(*)
FROM ptpayors s1 JOIN payors s2 ON s1.payornum = s2.payornum JOIN 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
rv_charges a
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider", "Medicaid"
ORDER BY
"Provider", "Medicaid"
CategoryQueries
