Datasets and Queries Collection for OpenMRS 2x and RefApp 2x

Implementing
1

I want to create datasets/quesies for each section of data presented in RefApp 2x.

2

@arbaughj: Hi, could you please help me to write queries for above mentioned datasets?

3

Unfortunately, I am offline till near the end of the month. I might be able to help provide some content after that. But, you best bet would be to selicit help from organisations who already have reports like you are looking for.

4

Discharge Encounter data-set:

SELECT patient_identifier.identifier, 
DATE(person.date_created) AS register_date, 
concat(family_name, ' ', given_name) as fullname, 
birthdate, 
gender,
discharge_encounter.encounter_datetime as discharge_datetime,
(SELECT CONCAT(provider_name.family_name, ', ', provider_name.given_name) as provider FROM person_name as provider_name WHERE provider_name.preferred = 1 AND provider_name.voided = 0 AND provider_name.person_id = (SELECT person_id FROM provider WHERE provider_id = (SELECT provider_id FROM encounter_provider WHERE voided = 0 AND encounter_id = discharge_encounter.encounter_id LIMIT 1) LIMIT 1 ) LIMIT 1) as discharge_provider,
(SELECT location.name FROM location where location.location_id=discharge_encounter.location_id LIMIT 1) as discharge_location

FROM person, person_name, patient_identifier, encounter as discharge_encounter
WHERE person.person_id = :personId 
-- AND discharge_encounter.encounter_id = :encounterId
AND person.person_id = person_name.person_id 
AND person.person_id = patient_identifier.patient_id
AND person.voided=0 
AND person_name.voided=0 
AND patient_identifier.voided=0
AND person_name.preferred=1 
AND patient_identifier.preferred=1 
AND patient_identifier.identifier_type=3
AND patient_identifier.patient_id = discharge_encounter.patient_id
AND discharge_encounter.voided = 0 AND discharge_encounter.encounter_type = 3 
-- LIMIT 1;
1 Like
5

Admission Encounter Data-set:

SELECT patient_identifier.identifier, 
DATE(person.date_created) AS register_date, 
concat(family_name, ' ', given_name) as fullname, 
birthdate, 
gender,
admission_encounter.encounter_datetime as admission_datetime,
(SELECT CONCAT(provider_name.family_name, ', ', provider_name.given_name) as provider FROM person_name as provider_name WHERE provider_name.preferred = 1 AND provider_name.voided = 0 AND provider_name.person_id = (SELECT person_id FROM provider WHERE provider_id = (SELECT provider_id FROM encounter_provider WHERE voided = 0 AND encounter_id = admission_encounter.encounter_id LIMIT 1) LIMIT 1 ) LIMIT 1) as admission_provider,
(SELECT location.name FROM location where location.location_id=admission_encounter.location_id LIMIT 1) as admission_location

FROM person, person_name, patient_identifier, encounter as admission_encounter
WHERE person.person_id = :person 
-- AND discharge_encounter.encounter_id = :encounterId
AND person.person_id = person_name.person_id 
AND person.person_id = patient_identifier.patient_id
AND person.voided=0 
AND person_name.voided=0 
AND patient_identifier.voided=0
AND person_name.preferred=1 
AND patient_identifier.preferred=1 
AND patient_identifier.identifier_type=3
AND patient_identifier.patient_id = admission_encounter.patient_id
AND admission_encounter.voided = 0 AND admission_encounter.encounter_type = 4 
-- LIMIT 1;
1 Like
6

Visit Note Data-set:

SELECT patient_identifier.identifier, 
DATE(person.date_created) AS register_date, 
concat(family_name, ' ', given_name) as fullname, 
birthdate, 
gender,
visit_note_encounter.encounter_datetime as visit_note_datetime,
(SELECT CONCAT(provider_name.family_name, ', ', provider_name.given_name) as provider FROM person_name as provider_name WHERE provider_name.preferred = 1 AND provider_name.voided = 0 AND provider_name.person_id = (SELECT person_id FROM provider WHERE provider_id = (SELECT provider_id FROM encounter_provider WHERE voided = 0 AND encounter_id = visit_note_encounter.encounter_id LIMIT 1) LIMIT 1 ) LIMIT 1) as visit_note_provider,
(SELECT location.name FROM location where location.location_id=visit_note_encounter.location_id LIMIT 1) as visit_note_location,
(SELECT clinic_note.value_text FROM obs AS clinic_note WHERE clinic_note.voided= 0 AND clinic_note.concept_id=162169 AND clinic_note.encounter_id=visit_note_encounter.encounter_id LIMIT 1) AS visit_note_clinic_note

FROM person, person_name, patient_identifier, encounter as visit_note_encounter
WHERE person.person_id = :person 
-- AND visit_note_encounter.encounter_id = :encounterId
AND person.person_id = person_name.person_id 
AND person.person_id = patient_identifier.patient_id
AND person.voided=0 
AND person_name.voided=0 
AND patient_identifier.voided=0
AND person_name.preferred=1 
AND patient_identifier.preferred=1 
AND patient_identifier.identifier_type=3
AND patient_identifier.patient_id = visit_note_encounter.patient_id
AND visit_note_encounter.voided = 0 AND visit_note_encounter.encounter_type = 5 
-- LIMIT 1;
1 Like
7

Capture Vital Data-set:

SELECT patient_identifier.identifier, 
DATE(person.date_created) AS register_date, 
concat(family_name, ' ', given_name) as fullname, 
birthdate, 
gender,
vital_encounter.encounter_datetime as vital_datetime,
(SELECT CONCAT(provider_name.family_name, ', ', provider_name.given_name) as provider FROM person_name as provider_name WHERE provider_name.preferred = 1 AND provider_name.voided = 0 AND provider_name.person_id = (SELECT person_id FROM provider WHERE provider_id = (SELECT provider_id FROM encounter_provider WHERE voided = 0 AND encounter_id = vital_encounter.encounter_id LIMIT 1) LIMIT 1 ) LIMIT 1) as vital_provider,
(SELECT location.name FROM location where location.location_id=vital_encounter.location_id LIMIT 1) as vital_location,

ifnull((SELECT CONCAT(vital.value_numeric, 'cm') FROM obs AS vital 
WHERE vital.voided= 0 
AND vital.concept_id=5090
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_cm') AS "height_en",

ifnull((SELECT CONCAT(vital.value_numeric, 'kg') FROM obs AS vital 
WHERE vital.voided= 0 
AND vital.concept_id=5089 
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_kg') AS "weight_en",

ifnull((SELECT CONCAT(vital.value_numeric) FROM obs AS vital 
WHERE vital.voided= 0 
AND vital.concept_id=5088 
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_') AS "temperature_en",

ifnull((SELECT CONCAT(vital.value_numeric, '/min') FROM obs AS vital 
WHERE vital.voided= 0
AND vital.concept_id=5087 
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_/min') AS "pulse_en",

ifnull((SELECT CONCAT(vital.value_numeric, '/min') FROM obs AS vital 
WHERE vital.voided= 0 
AND vital.concept_id=5242
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_/min') AS "respiratory_rate_en",

ifnull((SELECT CONCAT(vital.value_numeric, '/100') FROM obs AS vital
WHERE vital.voided= 0
AND vital.concept_id=5085 
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_/100') AS "systolic_blood_pressure_en",

ifnull((SELECT CONCAT(vital.value_numeric, '/100') FROM obs AS vital 
WHERE vital.voided= 0 
AND vital.concept_id=5086  
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_/100') AS "diastolic_blood_pressure_en",

ifnull((SELECT CONCAT(vital.value_numeric, '%') FROM obs AS vital 
WHERE vital.voided= 0 
AND vital.concept_id=5092  
AND vital.encounter_id = vital_encounter.encounter_id
LIMIT 1), '_%') AS "blood_oxygen_saturation_en"

FROM person, person_name, patient_identifier, encounter as vital_encounter
WHERE person.person_id = :person 
-- AND vital_encounter.encounter_id = :encounterId
AND person.person_id = person_name.person_id 
AND person.person_id = patient_identifier.patient_id
AND person.voided=0 
AND person_name.voided=0 
AND patient_identifier.voided=0
AND person_name.preferred=1 
AND patient_identifier.preferred=1 
AND patient_identifier.identifier_type=3
AND patient_identifier.patient_id = vital_encounter.patient_id
AND vital_encounter.voided = 0 AND vital_encounter.encounter_type = 2 
-- LIMIT 1;
1 Like
8

Transfer To Ward/Service Data-set:

SELECT patient_identifier.identifier, 
DATE(person.date_created) AS register_date, 
concat(family_name, ' ', given_name) as fullname, 
birthdate, 
gender,
transfer_encounter.encounter_datetime as transfer_datetime,
(SELECT CONCAT(provider_name.family_name, ', ', provider_name.given_name) as provider FROM person_name as provider_name WHERE provider_name.preferred = 1 AND provider_name.voided = 0 AND provider_name.person_id = (SELECT person_id FROM provider WHERE provider_id = (SELECT provider_id FROM encounter_provider WHERE voided = 0 AND encounter_id = transfer_encounter.encounter_id LIMIT 1) LIMIT 1 ) LIMIT 1) as transfer_provider,
(SELECT location.name FROM location where location.location_id=transfer_encounter.location_id LIMIT 1) as transfer_to_location

FROM person, person_name, patient_identifier, encounter as transfer_encounter
WHERE person.person_id = :person 
-- AND discharge_encounter.encounter_id = :encounterId
AND person.person_id = person_name.person_id 
AND person.person_id = patient_identifier.patient_id
AND person.voided=0 
AND person_name.voided=0 
AND patient_identifier.voided=0
AND person_name.preferred=1 
AND patient_identifier.preferred=1 
AND patient_identifier.identifier_type=3
AND patient_identifier.patient_id = transfer_encounter.patient_id
AND transfer_encounter.voided = 0 AND transfer_encounter.encounter_type = 7 
-- LIMIT 1;
1 Like
9

Allergies Data-set:

SELECT allergy.allergen_type,
(SELECT allergy_name.name FROM concept, concept_name AS allergy_name WHERE allergy_name.concept_name_type = 'FULLY_SPECIFIED' AND concept.retired=0 AND concept.concept_id=allergy_name.concept_id AND allergy_name.voided= 0 AND allergy_name.locale='en'  AND allergy_name.concept_id=allergy.coded_allergen LIMIT 1) AS coded_allergen,
(SELECT GROUP_CONCAT(allergy_reaction_name.name SEPARATOR ',') FROM allergy_reaction, concept_name AS allergy_reaction_name WHERE allergy_reaction_name.concept_name_type = 'FULLY_SPECIFIED' AND allergy_reaction_name.voided= 0 AND allergy_reaction_name.concept_id=allergy_reaction.reaction_concept_id AND allergy_reaction_name.locale='en' AND allergy_reaction.allergy_id=allergy.allergy_id) AS allergy_reaction

FROM allergy
WHERE allergy.patient_id = :person
-- LIMIT 1;
1 Like
10

Visits Data-set:

SELECT visit.visit_id,
visit.date_created,
visit.date_started,
visit.date_stopped,
(SELECT location.name FROM location WHERE location.location_id=visit.location_id LIMIT 1) AS visit_location

FROM visit, visit_type, care_setting
WHERE visit.patient_id = :person
AND visit.visit_type_id=visit_type.visit_type_id 
AND visit.`voided`=0
-- LIMIT 1;
1 Like
11

@hpardess i thought you were doing these on qa-refapp.openmrs.org

12

I am testing these queries in qa-refapp.openmrs.org. but i also post it here to share with others. because someone may change/delete the query on demo server.