Script for identifying error scenario in Drug-o-gram

Error Scenario

When drugs were entered in non chronological order in 1.2, after upgrading to 1.4 the stop sign for some patients wasn't displayed on the drug-o-gram.

Fix

Below is the query to identify those patients and to rectify the order of the drugs to chronological order.

This is available in release 1.4 only

Query

SELECT 
pi.identifier, 
ppa.value_reference AS 'Treatment ID', 
o1.order_id AS 'ORDER1 ID', 
cn1.name AS 'Start Date Drug', 
DATE(o1.scheduled_date) AS 'Drug Start Date', 
cn2.name AS 'End date Drug', 
o2.order_id AS 'ORDER2 ID', 
DATE(o2.date_stopped) AS 'Drug End Date' 
FROM orders o1 
JOIN episode_encounter ee1 
ON ee1.encounter_id = o1.encounter_id AND o1.order_action != "DISCONTINUE" AND o1.voided = 0 
JOIN orders o2 
ON o2.order_action != "DISCONTINUE" AND o2.voided = 0 AND DATE(o2.date_stopped) = DATE(o1.scheduled_date) 
JOIN concept_name cn1 
ON cn1.concept_id = o1.concept_id AND cn1.concept_name_type = 'FULLY_SPECIFIED' AND cn1.voided = 0 
JOIN concept_name cn2 
ON cn2.concept_id = o2.concept_id AND cn2.concept_name_type = 'FULLY_SPECIFIED' AND cn2.voided = 0 
JOIN episode_encounter ee2 ON ee2.encounter_id = o2.encounter_id AND ee2.episode_id = ee1.episode_id 
JOIN episode_patient_program epp ON epp.episode_id = ee1.episode_id 
JOIN patient_program pp ON epp.patient_program_id = pp.patient_program_id AND pp.voided=0 
JOIN patient_identifier pi ON pi.patient_id = pp.patient_id AND pi.identifier_type = 3 AND pi.voided = 0 
JOIN patient_program_attribute ppa ON ppa.patient_program_id = pp.patient_program_id AND ppa.voided = 0 
JOIN program_attribute_type pat 
ON ppa.attribute_type_id = pat.program_attribute_type_id AND pat.name = 'Registration Number' 
WHERE o1.order_id < o2.order_id;