You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 6
Next »
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.
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;