Sample Integrity Checks

Owned by Jeremy Keiper
Last updated: Jul 09, 2012Version comment

Until the Data Integrity Module works with Metadata Sharing Module, please see the following list of sample SQL queries used by OpenMRS implementations to detect data quality issues:

Most of these checks were taken from the AMPATH Data Integrity Module wiki page ... please see site for updated list, or contribute your own!

Facility

Description

Code (double-click to copy)

AMPATH

encounter_date > date_created 

SELECT
encounter.encounter_id,
encounter.patient_id,
encounter.encounter_datetime,
encounter.date_created
FROM
encounter
Inner Join patient ON patient.patient_id = encounter.patient_id
WHERE
encounter.voided =  0 AND
encounter.date_created <  encounter.encounter_datetime AND
patient.voided =  0 AND
encounter.patient_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.voided =  0 AND
person_attribute.person_attribute_type_id =  28)
ORDER BY
encounter.encounter_datetime DESC

AMPATH

Questionable date of death 

SELECT
person.person_id,
person.death_date
FROM
person
WHERE
person.death_date IS NOT NULL  AND
person.voided =  0 AND
(YEAR(person.death_date) <  2001 OR
person.death_date >  NOW() ) AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
person.person_id DESC

AMPATH

Any encounter (except outreach field follow-up form (encounter_type=21) and death reporting form (encounter_type=31) is recorded after patient's date of death 

SELECT
encounter.encounter_id,
encounter.encounter_datetime,
person.death_date,
person.dead
FROM
encounter
Inner Join person ON encounter.patient_id = person.person_id
WHERE
person.death_date is not NULL AND
person.dead=1 AND
encounter.encounter_datetime >  person.death_date AND
encounter.voided =  '0' AND
person.voided =  '0' AND
encounter.encounter_type NOT IN  (21, 31) AND
person.person_id NOT IN (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.voided =  0 AND
person_attribute.person_attribute_type_id =  28)
ORDER BY
encounter.encounter_datetime DESC

AMPATH

DOB is null or blank 

SELECT
person.person_id
FROM
person
Inner Join patient ON person.person_id = patient.patient_id
WHERE
(person.birthdate IS NULL  OR
person.birthdate =  '') AND
person.voided =  0 AND
patient.voided =  0 AND
person.person_id NOT IN (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.voided =  0 AND
person_attribute.person_attribute_type_id =  28)
ORDER BY person_id DESC

AMPATH

Questionable DOB 

SELECT
person.person_id,
person.birthdate
FROM
person
WHERE
person.birthdate IS NOT NULL  AND
person.voided =  0 AND
(YEAR(person.birthdate) <  1910 OR
person.birthdate >  NOW() ) AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
person.person_id DESC

AMPATH

encounter_date < DOB 

SELECT
person.person_id,
person.birthdate,
encounter.encounter_id,
encounter.encounter_datetime
FROM
person
Inner Join encounter ON person.person_id = encounter.patient_id
WHERE
person.birthdate IS NOT NULL AND
person.birthdate > encounter.encounter_datetime AND
person.voided =  0 AND
encounter.voided =  0 AND
person.person_id NOT IN (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
encounter.encounter_datetime DESC

AMPATH

adult initial or return encounter_date when age < 10 years

 
SELECT
encounter.encounter_id,
encounter.patient_id,
person.birthdate,
encounter.encounter_datetime
FROM
person
Inner Join encounter ON person.person_id = encounter.patient_id
WHERE
/**adult encounter type**/
encounter.encounter_type IN  (1, 2, 14) AND
encounter.voided =  0 AND
person.voided =  0 AND
person.birthdate IS NOT NULL AND
(YEAR (encounter.encounter_datetime) - YEAR (person.birthdate)) < 10
AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
encounter.encounter_datetime DESC

AMPATH

peds initial or return encounter_date when age > 18 years

 
SELECT
encounter.encounter_id,
encounter.patient_id,
person.birthdate,
encounter.encounter_datetime
FROM
person
Inner Join encounter ON person.person_id = encounter.patient_id
WHERE
/**Peds encounter type**/
encounter.encounter_type IN  (3,4,15) AND
encounter.voided =  0 AND
person.voided =  0 AND
person.birthdate IS NOT NULL AND
(YEAR (encounter.encounter_datetime) - YEAR (person.birthdate)) >18 AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
encounter.encounter_datetime DESC

AMPATH

>1 same encounter form entered on the same date for the same patient (obs could be same or different when comparing those encounters)

This should be handled by using Double Entry Reconciliation Module when obs from those two encounters are different.

 
/** Use the following query when those encounters have the same obs **/

SELECT
a.encounter_id AS encounter_id_1, b.encounter_id AS encounter_id_2, a.patient_id, a.encounter_datetime, a.encounter_type
FROM encounter a
JOIN
encounter b
ON
a.patient_id=b.patient_id
WHERE
a.encounter_type in (1, 2, 3, 4, 14, 15) AND
a.encounter_id != b.encounter_id AND
a.encounter_type = b.encounter_type AND
a.encounter_datetime = b.encounter_datetime AND
a.voided = 0 AND
a.patient_id NOT IN (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
a.encounter_datetime DESC

AMPATH

when patient has both adult and peds form entered on the same date

This should be handled by using Double Entry Reconciliation Module .

 
SELECT
e1.patient_id,
e1.encounter_datetime,
e1.encounter_id,
e1.encounter_type,
e1.creator,
e2.encounter_id,
e2.encounter_type,
e2.creator
FROM
encounter e1
Inner Join encounter e2 ON e1.patient_id = e2.patient_id
WHERE
e1.voided = 0 AND
e1.encounter_type in (1, 2, 14) AND
e2.encounter_type in (3, 4, 15) AND
e1.encounter_datetime=e2.encounter_datetime
ORDER BY e1.encounter_datetime DESC

AMPATH

Questionable male patients* when gender is male and

  • pregnant=yes
  • number of pregnancy>0
  • gestation weeks>0
  • gestation months>0
  • last pregnancy outcome is not null
  • last menstrual period date is not null
 
SELECT
obs.obs_id,
obs.person_id,
person.gender
FROM
obs
Inner Join person ON obs.person_id = person.person_id
WHERE
person.voided =  0 AND
obs.voided =  0 AND
person.gender =  'M' AND
person.voided = 0 AND
(
(obs.concept_id =  2056 AND obs.value_coded >  1065) OR
(obs.concept_id =  1053 AND obs.value_numeric >  0) OR
(obs.concept_id=45 AND obs.value_coded=664) OR
(obs.concept_id =  1279 AND obs.value_numeric >  0) OR
(obs.concept_id =  5992 AND obs.value_numeric >  0) OR
(obs.concept_id =  5272 AND obs.value_numeric =1) OR
(obs.concept_id =  1790 AND obs.value_coded=44) OR
(obs.concept_id =  6042 AND obs.value_coded=1484)
) AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
obs.obs_datetime DESC

AMPATH

Questionable female patients* when gender is female and total number of children sired >0 

SELECT
obs.obs_id,
obs.person_id,
person.gender
FROM
obs
Inner Join person ON obs.person_id = person.person_id
WHERE
obs.voided =  0 AND
person.gender =  'F' AND
person.voided = 0 AND
(obs.concept_id=5558 AND obs.value_numeric>0)  AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
obs.obs_datetime DESC

AMPATH

age<10 years old and weight>50kg 

SELECT
obs.obs_id,
obs.value_numeric
FROM
person
Inner Join obs ON person.person_id = obs.person_id
WHERE
person.voided =  0 AND
obs.voided =  0 AND
person.birthdate IS NOT NULL AND
YEAR(obs.obs_datetime) - YEAR(person.birthdate) < 10 AND
obs.concept_id = 5089 AND
/**restrict to weight > 50kg for now to clean up the extreme outliers first before revising it back to weight > 40kg**/
obs.value_numeric > 50  AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
obs.obs_datetime DESC

AMPATH

age>18 years old and weight<20kg 

SELECT
obs.obs_id,
obs.value_numeric
FROM
person
Inner Join obs ON person.person_id = obs.person_id
WHERE
person.voided =  0 AND
obs.voided =  0 AND
person.birthdate IS NOT NULL AND
YEAR(obs.obs_datetime) - YEAR(person.birthdate) >18 AND
obs.concept_id = 5089 AND
/*temporary to restrict the weight to 20kg to clean up the extreme cases first*/
obs.value_numeric < 20  AND
person.person_id not in (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
obs.obs_datetime DESC

AMPATH

Questionable DNA PCR 

SELECT
obs.obs_id, obs.obs_datetime, obs.date_created
FROM
obs
where obs.concept_id = 1030  and obs.voided=0 and
(
(obs.obs_datetime >= DATE(NOW()) or obs.obs_datetime <= '2001-01-01' ) or
(obs.obs_datetime > obs.date_created)
)
order by obs.obs_datetime

AMPATH

Questionable HIV Eliza 

SELECT
obs.obs_id, obs.obs_datetime, obs.date_created
FROM
obs
where obs.concept_id = 1042  and obs.voided=0 and
(
(obs.obs_datetime >= DATE(NOW()) or obs.obs_datetime <= '2001-01-01' ) or
(obs.obs_datetime > obs.date_created)
)
order by obs.obs_datetime

AMPATH

Questionable obs_datetime or questionable encounter_datetime (obs_datetime > encounter_datetime) 

SELECT
obs.obs_id,
obs.obs_datetime,
encounter.encounter_datetime,
obs.person_id
FROM
person
Inner Join encounter ON person.person_id = encounter.patient_id
Inner Join obs ON encounter.encounter_id = obs.encounter_id
WHERE
obs.obs_datetime > encounter.encounter_datetime AND
person.voided =  0 AND
encounter.voided =  0 AND
obs.voided =  0 AND
person.person_id NOT IN (
SELECT
DISTINCT(person_attribute.person_id)
FROM person_attribute
WHERE
person_attribute.person_attribute_type_id =  28 AND
person_attribute.voided =  0
)
ORDER BY
obs.obs_id DESC