SQL: MU State Group 1 Group

About

This SQL Code provides attestation results for states that were grouped into Group 1 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:

Caveats

Code

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

with t as
(
select 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 and patno >99

)  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( (c.medicaid_num / c.total_enc)*100,1) as medicaid_pct from
(
select a.*, b.non_medicaid_num, a.Medicaid_num + b.non_medicaid_num as total_enc from
(
select 'Practice Totals' as r, t.encounters as Medicaid_num from t
where det = 'Medicaid') a
left outer join (select 'Practice Totals' as r, t.encounters as non_Medicaid_num from t where det = 'not Medicaid') b on a.r = b.r
) c