SQL: North Carolina Lead Registry

There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator.

About

This SQL will be used for North Carolina’s Lead Registry reporting. The report will run and produce all lead tests into a CSV file that can be uploaded to NCDHHS.

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

Caveats

  • Both the order line of your lead test and the result line of your lead test must be ‘XLEAD’.
  • You must have billed 83655 on the same day for the test (the primary insurance that was billed is reported in “medicaid,” whether or not it was a Medicaid plan).
  • It assumes all blood draws were capillary.
  • The 83655 must have been billed and be an archived transaction. If an 83655 exists, but as part of a superbill or a current claim (not yet archived), that row will not appear.

If any of these caveats are not true for your practice, the report will need to be adjusted to give accurate results. If you require assistance customizing the report please reach out to a Client Advocate for the cost.

SQL Code: Firebird

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

Select location.clia_ID as LAB_ID, loc_name as LAB_NAME, loc_address_1 as LAB_ADDR, loc_address_2 as LAB_ADDR2, loc_state as LAB_STATE,  loc_city as LAB_CITY, loc_zip_code as LAB_ZIP, loc_phone as LAB_PHONE,

register.lname as pt_lastname, register.fname as pt_firstname, register.mi as pt_middleinitial, register.birthdat as dob, register.sex, register.address as pt_addr, register.address1 as pt_addr2, 'US' as pt_country, register.state as pt_state,

ub.description1 as pt_county, register.city as pt_city,register.zipcode as pt_zip, register.hphone as pt_phone,

case when register.race = '16' then 'W' when register.race = '2' then 'A' when register.race = '4' then 'B' when register.race = '1' then 'N' when register.race = '8' then 'P' else 'O'

end as race,

case when e.ref_sid = 'N' then 'NH' else ref_sid end as ethnic,

dtoh.date1 as spec_dt, dto.trnsxno as spec_id,   'capillary' as sample_type, dtoh.date1 as analysis_date,

coalesce(results_alpha,results_numeric) as pb_result, '' as comment,

ic.ins_carrier_name as healthplanname,

at1.insured_id as medicaid,

drlabel.provider_npid as prov_id, loc_name as prov_facility, drlabel.fname as prov_fname, drlabel.lname as prov_lname, '' as prov_middleinitial,

loc_address_1 as PROV_ADDR, loc_address_2 as PROV_ADDR2, loc_state as PROV_STATE, loc_city as PROV_CITY,

loc_zip_code as PROV_ZIP, loc_phone as PROV_PHONE, loc_fax as PROV_FAXNUM,

register.PORGLNM as PGLNAME, PORGFNM as PGFNAME, P1ADD1 as PGADDRESS, P1ADD2 as PGADDRESS2, 'US' as PGCOUNTRY, P1ST as PGSTATE, P1CITY as PGCITY, P1ZIP as PGZIP, P1HPHONE as PGPHONE

from location

inner join

(select * from diag_test_order_header where date1 between :Start_date and :end_date) dtoh on dtoh.loc_id = location.id

inner join (select patno, date1, cptcode, ins_carrier_code,  insured_id, ins_carrier_code_other, insured_id_other from archive_transactions where cptcode = '83655' and date1 between :start_date and :end_date and archive_flag = 1) at1 on at1.patno = dtoh.patno and at1.date1 = dtoh.date1

inner join (select ins_carrier_code, ins_carrier_name, claim_filing_code from ins_carrier) ic on ic.ins_carrier_code =at1.ins_carrier_code

inner join register on register.patno = dtoh.patno

inner join drlabel on drlabel.addr_id = dtoh.reqby_addr_id

inner join diag_test_order dto on dto.diag_test_order_header_id = dtoh.diag_test_order_header_id

inner join diag_test_result dtr on dtr.orderlink = dto.trnsxno

left outer join (select ref_id, ref_sid from code_table where group_id = 24) e on e.ref_id = register.ethnicity

left outer join upif_borough ub on ub.code1 = register.county_code

where location.archive_flag = 0 and test_id = 'XLEAD'


SQL Code: MySQL

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

Select location.clia_ID as LAB_ID, loc_name as LAB_NAME, loc_address_1 as LAB_ADDR, loc_address_2 as LAB_ADDR2, loc_state as LAB_STATE,  loc_city as LAB_CITY, loc_zip_code as LAB_ZIP, loc_phone as LAB_PHONE,
register.lname as pt_lastname, register.fname as pt_firstname, register.mi as pt_middleinitial, register.birthdat as dob, register.sex, register.address as pt_addr, register.address1 as pt_addr2, 'US' as pt_country, register.state as pt_state,
ub.description1 as pt_county, register.city as pt_city,register.zipcode as pt_zip, register.hphone as pt_phone,
case when register.race = '16' then 'W' when register.race = '2' then 'A' when register.race = '4' then 'B' when register.race = '1' then 'N' when register.race = '8' then 'P' else 'O'
end as race,
case when e.ref_sid = 'N' then 'NH' else ref_sid end as ethnic,
dtoh.date1 as spec_dt, dto.trnsxno as spec_id,   'capillary' as sample_type, dtoh.date1 as analysis_date,
coalesce(results_alpha,results_numeric) as pb_result, '' as comment,
ic.ins_carrier_name as healthplanname,
at1.insured_id as medicaid,
drlabel.provider_npid as prov_id, loc_name as prov_facility, drlabel.fname as prov_fname, drlabel.lname as prov_lname, '' as prov_middleinitial,
loc_address_1 as PROV_ADDR, loc_address_2 as PROV_ADDR2, loc_state as PROV_STATE, loc_city as PROV_CITY,
loc_zip_code as PROV_ZIP, loc_phone as PROV_PHONE, loc_fax as PROV_FAXNUM,

register.PORGLNM as PGLNAME, PORGFNM as PGFNAME, P1ADD1 as PGADDRESS, P1ADD2 as PGADDRESS2, 'US' as PGCOUNTRY, P1ST as PGSTATE, P1CITY as PGCITY, P1ZIP as PGZIP, P1HPHONE as PGPHONE
from location

inner join

(select * from diag_test_order_header where date1 between :start_date and :end_date) dtoh on dtoh.loc_id = location.id

inner join (select patno, date1, cptcode, ins_carrier_code,  insured_id, ins_carrier_code_other, insured_id_other from archive_transactions where cptcode = '83655' and
 date1 between :start_date and :end_date and archive_flag = 1) at1 on at1.patno = dtoh.patno and at1.date1 = dtoh.date1

inner join (select ins_carrier_code, ins_carrier_name, claim_filing_code from ins_carrier) ic on ic.ins_carrier_code =at1.ins_carrier_code
inner join register on register.patno = dtoh.patno
inner join drlabel on drlabel.addr_id = dtoh.reqby_addr_id
inner join diag_test_order dto on dto.diag_test_order_header_id = dtoh.diag_test_order_header_id
inner join diag_test_result dtr on dtr.orderlink = dto.trnsxno
left outer join (select ref_id, ref_sid from code_table where group_id = 24) e on e.ref_id = register.ethnicity
left outer join upif_borough ub on ub.code1 = register.county_code
where location.archive_flag = 0 and dtr.test_id = 'XLEAD'