Uploaded image for project: 'Reporting Module'
  1. Reporting Module
  2. REPORT-427

Support Cohort Queries the compare the values of 2 Obs with the same question for each patient



    • Type: Bug
    • Status: Design
    • Priority: TBD
    • Resolution: Unresolved
    • Affects Version/s: Reporting 0.7.2
    • Fix Version/s: Future
    • Component/s: Cohort Definitions
    • Labels:
    • Complexity:


      In Rwanda, we have a couple of examples of queries where we need to find patients who have had a particular change in value for the answer to a question. For example:

      1. Find patients whose most recent CD4 count shows a 50% decrease from the greatest CD4 count that has been recorded since the initiation of ART.

      Lara has a Sql Cohort Definition to accomplish this with the following query, but a) it is rather complicated and b) it is very slow. Investigate whether a Java-based Cohort Definition (finding the most recent CD4 and the highest CD4 since ART initiation separately and comparing in code rather than sql), or the introduction of indices, or query optimization, could improve the performance of this query, and also see if we can provide an out of the box Definition that can make this easier to support generally:

      select 	p.patient_id 
      from 	patient p, person_attribute pa, person_attribute_type pat, obs o1, obs o2, patient_program pp, patient_state ps 
      where 	p.voided = 0 
      and 	p.patient_id = pa.person_id 
      and	pat.name = 'Health Center' 
      and 	pat.person_attribute_type_id = pa.person_attribute_type_id 
      and 	pa.voided = 0 and pa.value = :location
      and 	pp.voided = 0
      and	ps.voided = 0 
      and 	ps.patient_program_id = pp.patient_program_id 
      and 	pp.patient_id = p.patient_id 
      and 	ps.state = :state
      and	o1.concept_id = :concept
      and 	o1.obs_id = (
      		select 	obs_id 
      		from 	obs 
      		where 	voided = 0 
      		and 	p.patient_id = person_id
      		and 	concept_id = :concept
                      and 	value_numeric is not null 
      		and	obs_datetime >= ps.start_date 
      		order by value_numeric desc
      		LIMIT 1) 
      and	 o2.obs_id = (
      		select 	obs_id 
      		from 	obs 
      		where 	voided = 0 
      		and 	p.patient_id = person_id 
      		and	concept_id = :concept
      		and 	value_numeric is not null
      		and	obs_datetime >= ps.start_date 
      		and 	obs_datetime <= :beforeDate
      		order by obs_datetime desc 
      		LIMIT 1) 
      and ((o2.value_numeric/o1.value_numeric)*100) < 50");

      2. Also in Rwanda, there is a question around a patient's Asthma classification, where there is an Asthma Classification question (concept 7074) which has coded answers of:

      Intermittent Asthma (7071)
      Severe Persistent Asthma (7072)
      Moderate Persistent Asthma (7067)
      NOT INDICATED (2433)
      Mild persistent asthma (7073)
      Severe uncontrolled asthma (7069)

      They need to find the number of patients who have shown an increase in their Asthma Classification in the last quarter (ie so if the last obs returned was Moderate and the previous was Mild).

      So I was thinking it would be easier to pull out the most recent Asthma Classification and the second most recent separately, then use
      code to do the comparison

      These are the examples that we need to be able to support

        Gliffy Diagrams




              mseaton Mike Seaton
              0 Vote for this issue
              2 Start watching this issue