About

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

Caveats

• Provider decision to submit results as an individual.
• State is listed in group 2 (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 staffname, count(uniqid) as encounters, det
from
(
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 staffname, det
order by staffname)

select b.*,   round(( (  ((med_enc-chip_enc) / total_enc))*100),1) as medicaid_pct_post_adjustment    from
(
select staffname, med_enc, non_med_enc, med_enc+non_med_enc as total_enc, medicaid_pct_pre_adjustment, adj_factor, round(adj_factor * med_enc,0) as CHIP_enc  from
(
select distinct staffname, med_enc, non_med_enc, cast(:adjustment_factor as float) as adj_factor, round(((med_enc / (non_med_enc + med_enc))*100),1) as medicaid_pct_pre_adjustment from t
left outer join (select encounters as Med_enc, staffname from t where det  = 'Medicaid') m on m.staffname = t.staffname
left outer join (select encounters as non_med_enc, staffname from t where det = 'not Medicaid') n on n.staffname = t.staffname
where staffname <> ''
) 
) b