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. (in 1.4 only)

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;