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) 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
|
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 |