SQL: Visibility Cleanup: Problem List

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 SQL will produce a report listing PCP, Patient, ICD Code, SNOMED Code, and definition using the SQL. The report will list if the Problem List entry can be considered sensitive. 

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

Caveats

None.  By changing the keywords below (e.g. '%sexual%' and '%THC%') the practice can find other potentially-sensitive markers.

Code

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

select c.* from (
select  pcp, patno, fname, lname, icd_1, snomed1, words, privacy,
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 (words) like '%STI%' 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 '%addicition%' then 'sensitive'
when lower(words) like '%etoh%' then 'sensitive'
when lower(words) like '%queer%' then 'sensitive'
when lower(words) like '%weed%' then 'sensitive'
when lower(words) like '%grass%' then 'sensitive'
when upper(words) like '%MJ%' then 'sensitive'
when lower(words) like '%assault%' then 'sensitive'
else 'ok' end as sensitivity
from (
select a.*,   trim(coalesce(icd_1_descript, '') || ' ' || coalesce(snomed1_descript,"") || coalesce( note,"") ) as words  from (
select patno, fname,lname, staffname as pcp, icd_1, icd_1_descript, snomed1, snomed1_descript, cast(left(note1,5000) as char(5000)) as note,
case when chartnotes.privacy_level = 'A' then 'public' when chartnotes.privacy_level is null then 'public' else 'private' end as privacy from chartnotes

inner join register on register.patno = chartnotes.patno
inner join staff1 on staff1.staffid = register.addr_id
 where section_subheading = 'PAST MEDICAL HISTORY'
and patno > 99
and status_pat ='ACTIVE'
and lname <> 'TESTPATIENT'
and problem_list ='Y'
order by patno
) a ) b
) c