Revision [469]
This is an old revision of CountOfPatientsByInsurancePlan made by SethKrieger on 2011-08-16 09:44:21.Count Of Patients By Insurance Plan
Back to Query Directory
The SOS report “Patients by Insurance Carrier” lists every active patient. Can that report be trimmed to just giving the carrier and the number of active patients within each carrier, perhaps also with a grand total of active patients? Knowing the exact amount of patients we serve is helpful when negotiating with carriers about rates, etc.
The following query gives a count by Plan and (primary) Provider. For the count across providers, just remove the comma and d.provcode from the GROUP BY clause. Better yet, look at the second version of the query below, featuring a ROLLUP that gives you all the totals and subtotals without the need to edit.
SELECT
c.payorname AS "Ins Plan",
d.provcode,
count(a.ptnum) AS "N"
FROM
patients a
JOIN ptpayors b ON a.ptnum = b.ptnum
JOIN payors c ON b.payornum = c.payornum
JOIN providers d ON a.providernum = d.providernum
WHERE
a.flag = 0
AND a.dischargedate IS NULL
AND c.payortype = 'I'
GROUP BY
c.payorname, d.provcode
ORDER BY
c.payorname, d.provcode
c.payorname AS "Ins Plan",
d.provcode,
count(a.ptnum) AS "N"
FROM
patients a
JOIN ptpayors b ON a.ptnum = b.ptnum
JOIN payors c ON b.payornum = c.payornum
JOIN providers d ON a.providernum = d.providernum
WHERE
a.flag = 0
AND a.dischargedate IS NULL
AND c.payortype = 'I'
GROUP BY
c.payorname, d.provcode
ORDER BY
c.payorname, d.provcode
The version below adds the ROLLUP operator to the GROUP BY. That creates a result set that includes NULL in various cells. Read NULL as "ALL." Therefore if you see NULL in the ProvCode column, it means that this count is for all providers. There is also a row with NULL in both the Plan and Provider columns, meaning all plans and all providers, which in this case is the number of patients in the result set.
SELECT
c.payorname AS "Ins Plan",
d.provcode,
count(a.ptnum) AS "N"
FROM
patients a
JOIN ptpayors b ON a.ptnum = b.ptnum
JOIN payors c ON b.payornum = c.payornum
JOIN providers d ON a.providernum = d.providernum
WHERE
a.flag = 0
AND a.dischargedate IS NULL
AND c.payortype = 'I'
GROUP BY
ROLLUP(c.payorname, d.provcode)
ORDER BY
c.payorname, d.provcode
c.payorname AS "Ins Plan",
d.provcode,
count(a.ptnum) AS "N"
FROM
patients a
JOIN ptpayors b ON a.ptnum = b.ptnum
JOIN payors c ON b.payornum = c.payornum
JOIN providers d ON a.providernum = d.providernum
WHERE
a.flag = 0
AND a.dischargedate IS NULL
AND c.payortype = 'I'
GROUP BY
ROLLUP(c.payorname, d.provcode)
ORDER BY
c.payorname, d.provcode
CategoryQueries
