SQL: Visits With Possible Sensitive Diagnosis Codes

OP sets all defaults to share all information. Any individual decisions by Practice-users to restrict information sharing (access, use, or exchange) are the responsibility of the Practice in the implementation of its 21st Century Cures Act Information Blocking policies and procedures for its Practice and patients.

About

This report will look for diagnosis codes in Encounter and Well Visit notes that may be considered sensitive.

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


Caveats

This report does not read through the text of the notes only the diagnosis codes. In addition, the report can take a while to run and should be run during non-peak office hours. It is suggested that you run the report for one year at a time, evaluate the information returned, and make adjustments if necessary. It is the practice's responsibility to review the information on the report and determine if it should be shared.

Code

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

select b.*, case when private_sick = 'A' or private_well = 'A' then 'public' else 'private' end as privacy_level from (
select a.*, case when lower(words) like  '%abuse%' then 'sensitive'
when lower(words) like  '%drug expos%' then 'sensitive'
when lower(words) like  '%drug withd%' then 'sensitive'
when lower(words) like '%sexual%' then 'sensitive'
when lower(words) like '%marijuana%' then 'sensitive'
when (words) like '%THC%' then 'sensitive'
when lower(words) like '%gay%' then 'sensitive'
when lower(words) like '%compliance%' then 'sensitive'
when lower(words) like '%std%' then 'sensitive'
when lower(words) like '%chlamydia%' then 'sensitive'
when lower(words) like '%gonorrhea%' then 'sensitive'
when words like '%UDS%' then 'sensitive'
when lower(words) like '%suicid%' then 'sensitive'
when lower(words) like '%addiction%' then 'sensitive'
when lower(words) like '%etoh%' then 'sensitive'
when lower(words) like '%assault%' then 'sensitive'
else 'ok' end as sensitivity, enc_note.privacy_level as private_sick, physical.privacy_level as private_well
 from (
select patno, dx_date, dxcode, (dx_description) as words from dx_attach d
inner join register r on r.patno= d.patno
 where attach_table in ('PHYSICAL','ENC_NOTE') and dx_date between :Start_date and :End_Date
and lname <> 'TESTPATIENT' and patno >99 and status_pat = 'ACTIVE' ) a
left outer join enc_note on enc_note.patno = a.patno and enc_note.date1 = a.dx_date
left outer join physical on physical.patno = a.patno and physical.date1 = a.dx_date
) b where sensitivity = 'sensitive'