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

SQL: MU State Group 2 Drill Down

About

This SQL Code provides detailed attestation results at the patient level for states that were grouped into Group 2 in the MU: State Group SQL Table. The report provides patient-level detail for audit purposes.

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

  • Individual drill down requires the providers ID number to retrieve only that provider's data. If you prefer to select from a dropdown and filter to the provider, you will want to use the Group level detail report.
  • 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.
  • Provider decision to submit results as an individual.
  • State is listed in group 2 (see MU: State Group SQL Table and MU: State Assumptions).

Code

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

select distinct patient_date_of_service, patno, date_of_service, patient_name, staffname, b.ins_carrier_code,  ic1.ins_fullname, b.ins_carrier_code_other, ic2.ins_fullname,  medicaid_id, determination
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
order by patno, date_of_service