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 2 Group


This SQL Code provides attestation results for states that were grouped into Group 2 in the MU: State Group SQL Table. The report is based on the provider's decision to provide group 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(uniqid) as encounters, det
select distinct uniqid, 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 det from 
  select patno, rend_addr_id, date1, (patno || ' ' || date1) as uniqid, cptcode, at1.ins_carrier_code, at1.ins_carrier_code_other from archive_transactions at1
 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
group by det

select c.*,   round(( (  ((med_enc-chip_enc) / total_enc))*100),1) as medicaid_pct_post_adjustment 
select b.*, round(adj_factor * med_enc,0) as CHIP_enc from

select a.*, med_enc + non_med_enc as total_enc, round(((med_enc / (non_med_enc + med_enc))*100),1) as medicaid_pct_pre_adjustment,  cast(:adjustment_factor as float) as adj_factor from

select m.*, n.non_med_enc from (select 'Practice Totals' as r, encounters as Med_enc  from t where det  = 'Medicaid') m 
left outer join (select  'Practice Totals' as r,  encounters as non_med_enc from t where det = 'not Medicaid') n on n.r =m.r
) a
) c