Important Content Update Message
We are currently updating the OP Help Center content for OP 20. We appreciate your patience as we continue to make these updates. To locate the version of your software, navigate to: Help tab > About.

SQL: MU State Group 4 Individual


This SQL Code provides attestation results for states that were grouped into Group 4 in the MU: State Group SQL Table. The report is based on the provider's decision to provide individual attestation results.

Note: Export, save, and print all results for audit purposes at the time you run them for actual volume validation.

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



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

with t as 
(select count(patient_date_of_service) as encounters, determination, payment_status, staffname 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
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 ('XCLUD') 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, staffname

select e.*, round(  (( e.medicaid_num_paid+e.medicaid_zero_paid) / e.total_enc)*100,1) as medicaid_pct from
select d.*, d.medicaid_num_paid + d.non_medicaid_enc + d.medicaid_zero_paid  as total_enc from
select a.*, coalesce(c.medicaid_zero_pay,0) as medicaid_zero_paid, coalesce(b.non_medicaid_num,0) as non_medicaid_enc from
select staffname as r, coalesce(t.encounters,0) as Medicaid_num_paid from t
where determination = 'Medicaid' and payment_status = 'Medicaid paid') a
left outer join (select staffname as r, coalesce(t.encounters,0) as non_Medicaid_num from t where determination = 'not Medicaid') b on a.r = b.r
left outer join (select staffname 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
) e