Hello, I am trying to get the values obtained by a visit note form and also be able to get the elements of the diagnosis for report purposes, Please I need help on the particular query to execute to be able to get that information.
Actually i don’t understand how the to fetch the elements, OR should i say i wish to know how to display the various items of the diagnoses on the visit note under patient information
Thank you very much
Hi @eugene23,
If I understand your question, you are looking for an SQL query that you could use to get the diagnosis with the Reporting Module.
This is an example giving a list of users and their addresses and their diagnosis’ using the CIEL concepts as used with the Reference App…
SELECTperson.person_id,diag.encounter_id,diag.obs_datetime, (SELECTnameFROMlocationWHERElocation.location_id=diag.location_id) as "Location",gender,birthdate,state_provinceas "Depatman",county_districtas "Seksyon Riral",city_villageas "Komin",address1as "Lokalitie",address2as "Ri / Nimewo Kay", (SELECTnameFROMconcept_nameWHEREvoided= 0 ANDlocale='en' ANDconcept_id=diag.value_codedANDconcept_name_type= 'FULLY_SPECIFIED' ORDER BYlocaleDESC LIMIT 1) as "Coded Diagnosis (en)", (SELECTnameFROMconcept_nameWHEREvoided= 0 ANDlocaleIN ('en','fr') ANDconcept_id=diag.value_codedANDconcept_name_type= 'FULLY_SPECIFIED' ORDER BYlocaleDESC LIMIT 1) as "Coded Diagnosis (fr)" FROMperson,person_address,obsas diag,obsas diag_cert WHEREperson_address.voided= 0 ANDperson.voided= 0 ANDdiag.voided= 0 ANDdiag_cert.voided= 0 ANDperson_address.person_id=person.person_idANDperson.person_id=diag.person_idANDdiag.concept_id= '1284' ANDdiag.obs_group_id=diag_cert.obs_group_idANDdiag_cert.concept_id= '159394' ANDdiag_cert.value_coded= '160250' ANDdiag.obs_datetime >= :startDate ANDdiag.obs_datetime < addDate(:endDate,1)
This is an example query used with a Cohort Query for the Anemia diagnosis from the CIEL concept dictionary as used with the Reference App…
SELECT diagnosis.person_id FROM obs as diagnosis, obs as cert WHERE diagnosis.concept_id = ‘1284’ AND cert.concept_id = ‘159394’ AND cert.voided = ‘0’ AND diagnosis.voided = ‘0’ and cert.value_coded = ‘160250’ AND diagnosis.obs_group_id=cert.obs_group_id AND diagnosis.encounter_id=cert.encounter_id AND diagnosis.obs_datetime >= :startDate AND diagnosis.obs_datetime < addDate(:endDate,1) AND diagnosis.value_coded = ‘1226’
Hope this helps.
Hi @arbaughj.
I really like the second query and have tried it and it works well (changed the concept ids) My worry there is why you had two obs tables (diagnosis and cert).
I understand the value_coded, voided, concept_id but the join between the tables is not clear.
Regards, @ech310n
Hello @arbaughj thanks for the proposed queries, it quite helped a lot.
I went further to create an indicator with the query select count(*) from encounter where date_created >= :startDate AND date_created <= :endDate and location_id = :location which i ran and it gave some numbers. I inteded to use this indicator on a report, but when i add the indicator into a period indicator report, i get an error.
Below is a screenshot of the sample run and the creation of the indicator
the error says
Failed to convert value of type ‘java.lang.String’ to required type ‘org.openmrs.module.reporting.indicator.CohortIndicator’; nested exception is java.lang.IllegalArgumentException: Cannot convert value of type [java.lang.String] to required type [org.openmrs.module.reporting.indicator.CohortIndicator]: PropertyEditor [org.openmrs.module.reporting.propertyeditor.IndicatorEditor] returned inappropriate value of type [org.openmrs.module.reporting.indicator.SqlIndicator]
I am glad if you help Thanks
Glad you found the query helpful @eugene23.
@ech310n, the second join to the obs table was so I could pick out certain diagnosis types. I wanted confirmed diagnosis’ rather than presumed diagnosis’.
Concerning the error message, it looks like you’ve got a problem wit the type of a parameter, especially ‘java.lang.String’. Look in the indicator (and cohort) to ensure the type is right on all of the parameters. Somewhere you will find one of them has a type of string, where it should be a type location or date. Fix that and you should be set.
It’s better to use SQL cohorts than indicators to do that. So your sample SQL cohort will be like this:
select encounter_id from encounter where date_created >= :startDate AND date_created <= :endDate and location_id = :location
Take note of not using count(*). Openmrs automatically returns to you the number of records that match that query so the count(*) is not much needed.
A patient can have many visits. so how can we get the list of diagnosis of a specific visit?
