Posting for anyone that might find this query useful. Like the Insurance Distribution report but to the plan level of detail:
SELECT name AS Insurance, sb3.plan_name AS Plan, SUM(tot_charges) AS Charges, SUM(visits) AS Visits, SUM(patients) AS Patients, SUM(patients) / (SELECT count(distinct b.pid) FROM form_encounter AS fe, billing AS b WHERE fe.date >= '2022-01-01' AND fe.date <= NOW() AND b.pid = fe.pid AND b.encounter = fe.encounter AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0) * 100 as 'Pt %' FROM (SELECT COUNT(*) AS visits, SUM(charges) AS tot_charges, payor, plan_name FROM (SELECT b.pid, b.encounter, SUM(b.fee) AS charges, MAX(fe.date) AS date, id.provider as payor, id.plan_name FROM form_encounter AS fe, billing AS b INNER JOIN insurance_data AS id ON b.pid = id.pid WHERE fe.date >= '2022-01-01' AND fe.date <= NOW() AND b.pid = fe.pid AND b.encounter = fe.encounter AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 AND id.type = 'primary' GROUP BY b.pid, b.encounter, id.provider, id.plan_name) as sb1 GROUP BY payor, plan_name) AS sb3, (SELECT COUNT(*) AS patients, payor, plan_name FROM (SELECT b.pid, id.provider as payor, id.plan_name FROM form_encounter AS fe, billing AS b INNER JOIN insurance_data AS id ON b.pid = id.pid WHERE fe.date >= '2022-01-01' AND fe.date <= NOW() AND b.pid = fe.pid AND b.encounter = fe.encounter AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 AND id.type = 'primary' GROUP BY b.pid, id.provider, id.plan_name) as sb2 GROUP BY payor, plan_name) AS sb4, insurance_companies AS ic WHERE sb3.payor = sb4.payor AND sb3.plan_name = sb4.plan_name AND ic.id = sb3.payor GROUP BY Insurance, Plan UNION SELECT '' AS Insurance, sb3.plan_name AS Plan, SUM(tot_charges) AS Charges, SUM(visits) AS Visits, SUM(patients) AS Patients, SUM(patients) / (SELECT count(distinct b.pid) FROM form_encounter AS fe, billing AS b WHERE fe.date >= '2022-01-01' AND fe.date <= NOW() AND b.pid = fe.pid AND b.encounter = fe.encounter AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0) * 100 as 'Pt %' FROM (SELECT COUNT(*) AS visits, SUM(charges) AS tot_charges, payor, plan_name FROM (SELECT b.pid, b.encounter, SUM(b.fee) AS charges, MAX(fe.date) AS date, id.provider as payor, id.plan_name FROM form_encounter AS fe, billing AS b INNER JOIN insurance_data AS id ON b.pid = id.pid WHERE fe.date >= '2022-01-01' AND fe.date <= NOW() AND b.pid = fe.pid AND b.encounter = fe.encounter AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 AND id.type = 'primary' GROUP BY b.pid, b.encounter, id.provider, id.plan_name) as sb1 GROUP BY payor, plan_name) AS sb3, (SELECT COUNT(*) AS patients, payor, plan_name FROM (SELECT b.pid, id.provider as payor, id.plan_name FROM form_encounter AS fe, billing AS b INNER JOIN insurance_data AS id ON b.pid = id.pid WHERE fe.date >= '2022-01-01' AND fe.date <= NOW() AND b.pid = fe.pid AND b.encounter = fe.encounter AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 AND id.type = 'primary' GROUP BY b.pid, id.provider, id.plan_name) as sb2 GROUP BY payor, plan_name) AS sb4 WHERE sb3.payor = sb4.payor AND sb3.plan_name = sb4.plan_name AND sb3.payor = '' GROUP BY Insurance, Plan ORDER BY Insurance, Plan;
To use, substitute whichever range of dates are desired.