SQL: MU State Group 1 Drill Down

About

This SQL Code provides detailed attestation results at the patient level for states that were grouped into Group 1 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 1 (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, dob, 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, dob, 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, dob, 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, birthdat as dob, 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 patno >99 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