About

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.

Copy

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