Sample Integrity Checks
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) |
/** 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
|
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 |