About

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

• Provider decision to submit results as an group.
• State is listed in group 4 (see MU: State Group SQL Table and MU: State Assumptions).
• Depending on your billing, you may need to make some adjustments to the SQL code to exclude certain CPT codes from Encounter visits and/or to exclude some insurance payers as Medicaid.

Code

To highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

with t as 
(select count(patient_date_of_service) as encounters, determination, payment_status from
(
select distinct patient_date_of_service, b.patno, date_of_service, patient_name, staffname, b.ins_carrier_code, b.ins_carrier_code_other, ic1.ins_fullname, ic2.ins_fullname as other_ins_fullname,  medicaid_id, determination,
p.ins_carrier_code as payer_code, p.payment as medicaid_payment, case when determination = 'Medicaid' and payment >= 0.01 then 'Medicaid paid' when determination = 'Medicaid'  then 'Medicaid zero pay' else 'n/a' end as payment_status
from (
select patient_date_of_service, patno, patient_name, date_of_service, staffid, staffname, ins_carrier_code, ins_carrier_code_other, c1,c2, determination , case when determination = 'not Medicaid' then '-' when c1 = 'MC' then insured_id when c2 = 'MC' then insured_id_other else '*-' end as Medicaid_id
from
(
select distinct uniqid as patient_date_of_service, patno,  date1 as date_of_service, patient_name,  staff1.staffid, staff1.staffname, a.ins_carrier_code, a.ins_carrier_code_other, i1.claim_filing_code as c1, i2.claim_filing_code as c2, case when (i1.claim_filing_code = 'MC' or i2.claim_filing_code = 'MC') then 'Medicaid' else 'not Medicaid' end as determination, insured_id, insured_id_other from 
(
  select patno, ( fname || ' ' || lname) as patient_name,  rend_addr_id, date1, (patno || ' ' || date1) as uniqid, cptcode, at1.ins_carrier_code, at1.ins_carrier_code_other, at1.insured_id, at1.insured_id_other from archive_transactions at1
 inner join register on register.patno = at1.patno
 where at1.cptcode not in ('1','2','3','4') and pos not in (21,23)  and at1.cptcode not in ('113','99999','P1001') and at1.ins_carrier_code not in ('XCLUDE') and
  at1.date1 between :attestation_start and ( :attestation_start + 89) and at1.archive_flag = 1
)  a
 left outer join ins_carrier i1 on i1.ins_carrier_code = a.ins_carrier_code
 left outer join ins_carrier i2 on i2.ins_carrier_code = a.ins_carrier_code_other
 left outer join staff1 on staff1.staffid = a.rend_addr_id
order by determination
) 
) b
inner join ins_carrier ic1 on ic1.ins_Carrier_Code = b.ins_carrier_code
left outer join  ins_carrier ic2 on ic2.ins_carrier_code = b.ins_carrier_code_other


left outer join 
(select patno, dos, ins_carrier_code, round(coalesce(sum(paid),0),2) as payment from
(select at1.patno, at1.date1 as dos, at1.trnsxno, at2.ins_carrier_code, at2.paid from archive_transactions at1 left outer join (select txnopaid,  ins_carrier_code, sum(payment + copayrecd) as paid from archive_transactions where archive_transactions.date1 >=  :attestation_start  and (payment >= 0.01 or copayrecd >= 0.01) and archive_flag = 1  group by txnopaid,  ins_carrier_code ) at2 on at2.txnopaid=at1.trnsxno where at2.ins_carrier_code is not null  and at1.archive_flag = 1 and at1.cptcode not in ('113','99999','P1001') and  ( at2.ins_carrier_code in (select ins_carrier_code from ins_carrier where claim_filing_code = 'MC'        )   )  )
group by patno, dos,ins_carrier_code)
p on p.patno = b.patno and b.date_of_service = p.dos
) group by determination, payment_status
)


select d.*, round(  (( d.medicaid_num_paid+d.medicaid_zero_pay) / d.total_enc)*100,1) as medicaid_pct from
(
select a.*, c.medicaid_zero_pay, b.non_medicaid_num, a.medicaid_num_paid + b.non_medicaid_num + c.medicaid_zero_pay as total_enc from
(
select 'Practice Totals' as r, t.encounters as Medicaid_num_paid from t
where determination = 'Medicaid' and payment_status = 'Medicaid paid') a
left outer join (select 'Practice Totals' as r, t.encounters as non_Medicaid_num from t where determination = 'not Medicaid') b on a.r = b.r
left outer join (select 'Practice Totals' as r, coalesce(t.encounters,0) as Medicaid_zero_pay from t where determination = 'Medicaid'  and payment_status = 'Medicaid zero pay') c on a.r = c.r
) d