Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE caris_db;
- SET @start_date = '2019-07-01';
- SET @end_date = '2019-09-30';
- set @start_date_last = '2019-04-01';
- set @end_date_last = '2019-06-30';
- SELECT
- bot.patient_code,
- IF(club_q1 IS NOT NULL,
- 'yes',
- IF(quest_q1 IS NOT NULL,
- 'yes',
- IF(arv_1 IS NOT NULL,
- 'yes',
- IF(odk_1 IS NOT NULL,
- 'yes',
- IF(ptme_1 IS NOT NULL,
- 'yes',
- IF(ptme1_1 IS NOT NULL,
- 'yes',
- IF(mereenfant_1 IS NOT NULL,
- 'yes',
- 'no'))))))) AS q1,
- IF(club_q2 IS NOT NULL,
- 'yes',
- IF(quest_q2 IS NOT NULL,
- 'yes',
- IF(arv_2 IS NOT NULL,
- 'yes',
- IF(odk_2 IS NOT NULL,
- 'yes',
- IF(ptme_2 IS NOT NULL,
- 'yes',
- IF(ptme1_2 IS NOT NULL,
- 'yes',
- IF(mereenfant_2 IS NOT NULL,
- 'yes',
- 'no'))))))) AS q2,
- IF(club_q1 IS NOT NULL, 'yes', 'no') AS club_q1,
- IF(club_q2 IS NOT NULL, 'yes', 'no') AS club_q2,
- IF(quest_q1 IS NOT NULL, 'yes', 'no') AS quest_q1,
- IF(quest_q2 IS NOT NULL, 'yes', 'no') AS quest_q2,
- IF(arv_1 IS NOT NULL, 'yes', 'no') AS on_arv_q1,
- IF(arv_2 IS NOT NULL, 'yes', 'no') AS on_arv_q2,
- IF(odk_1 IS NOT NULL, 'yes', 'no') AS odk_q1,
- IF(odk_2 IS NOT NULL, 'yes', 'no') AS odk_q2,
- IF(COALESCE(ptme_1, ptme1_1) IS NOT NULL,
- 'yes',
- 'no') AS ptme_q1,
- IF(COALESCE(ptme_2, ptme1_2) IS NOT NULL,
- 'yes',
- 'no') AS ptme_q2,
- IF(mereenfant_1 IS NOT NULL,
- 'yes',
- 'no') AS mereenfant_q1,
- IF(mereenfant_2 IS NOT NULL,
- 'yes',
- 'no') AS mereenfant_2,
- office
- FROM
- (SELECT
- *
- FROM
- (SELECT
- patient_code
- FROM
- tracking_motherbasicinfo
- LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient) o UNION SELECT
- health_id AS patient_code
- FROM
- openfn.odk_pregnancy_visit
- WHERE
- date_of_visit BETWEEN @start_date_last AND @end_date) bot
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS club_q2
- FROM
- session
- LEFT JOIN club_session ON club_session.id = session.id_club_session
- LEFT JOIN patient ON patient.id = session.id_patient
- WHERE
- is_present = 1
- AND club_session.date BETWEEN @start_date AND @end_date) c2 ON club_q2 = bot.patient_code
- LEFT JOIN
- patient ON patient.patient_code = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS club_q1
- FROM
- session
- LEFT JOIN club_session ON club_session.id = session.id_club_session
- LEFT JOIN patient ON patient.id = session.id_patient
- WHERE
- is_present = 1
- AND club_session.date BETWEEN @start_date_last AND @end_date_last) c1 ON club_q1 = bot.patient_code
- LEFT JOIN
- (SELECT
- patient_code AS quest_q1, date
- FROM
- (SELECT
- id_patient, date
- FROM
- questionnaire_motherhivknowledge UNION SELECT
- id_patient, date
- FROM
- questionnaire_mothersurvey UNION SELECT
- id_patient, date
- FROM
- questionnaire_newmotherhivknowledge) x
- LEFT JOIN patient ON patient.id = x.id_patient
- WHERE
- date BETWEEN @start_date_last AND @end_date_last) quest ON quest_q1 = bot.patient_code
- LEFT JOIN
- (SELECT
- patient_code AS quest_q2
- FROM
- (SELECT
- id_patient
- FROM
- questionnaire_motherhivknowledge
- WHERE
- date BETWEEN @start_date AND @end_date UNION SELECT
- id_patient
- FROM
- questionnaire_mothersurvey
- WHERE
- date BETWEEN @start_date AND @end_date UNION SELECT
- id_patient
- FROM
- questionnaire_newmotherhivknowledge
- WHERE
- date BETWEEN @start_date AND @end_date) y
- LEFT JOIN patient ON patient.id = y.id_patient) ques ON quest_q2 = bot.patient_code
- LEFT JOIN
- lookup_hospital ON CONCAT(lookup_hospital.city_code,
- '/',
- lookup_hospital.hospital_code) = LEFT(patient.patient_code, 8)
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS arv_1
- FROM
- tracking_regime
- LEFT JOIN patient ON patient.id = id_patient
- WHERE
- (start_date BETWEEN @start_date_last AND @end_date_last
- OR end_date BETWEEN @start_date_last AND @end_date_last)
- AND category = 'regime_mother_treatment') ar ON arv_1 = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS arv_2
- FROM
- tracking_regime
- LEFT JOIN patient ON patient.id = id_patient
- WHERE
- (start_date BETWEEN @start_date AND @end_date
- OR end_date BETWEEN @start_date AND @end_date)
- AND category = 'regime_mother_treatment') arv ON arv_2 = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- health_id AS odk_1
- FROM
- openfn.odk_pregnancy_visit
- WHERE
- date_of_visit BETWEEN @start_date_last AND @end_date_last) odk1 ON odk_1 = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- health_id AS odk_2
- FROM
- openfn.odk_pregnancy_visit
- WHERE
- date_of_visit BETWEEN @start_date AND @end_date) odk2 ON odk_2 = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS ptme_1
- FROM
- tracking_pregnancy
- LEFT JOIN patient ON patient.id = id_patient_mother
- WHERE
- (ptme_enrollment_date BETWEEN @start_date_last AND @end_date_last)
- OR (actual_delivery_date BETWEEN @start_date_last AND @end_date_last)) ptme1 ON ptme_1 = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS ptme_2
- FROM
- tracking_pregnancy
- LEFT JOIN patient ON patient.id = id_patient_mother
- WHERE
- (ptme_enrollment_date BETWEEN @start_date AND @end_date)
- OR (actual_delivery_date BETWEEN @start_date AND @end_date)) ptme2 ON ptme_2 = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS ptme1_1
- FROM
- tracking_motherbasicinfo
- LEFT JOIN patient ON patient.id = id_patient
- WHERE
- PTME_date BETWEEN @start_date_last AND @end_date_last) pt1 ON ptme1_1 = bot.patient_code
- LEFT JOIN
- (SELECT DISTINCT
- patient_code AS ptme1_2
- FROM
- tracking_motherbasicinfo
- LEFT JOIN patient ON patient.id = id_patient
- WHERE
- PTME_date BETWEEN @start_date AND @end_date) pt2 ON ptme1_2 = bot.patient_code
- LEFT JOIN
- (SELECT
- patient.patient_code AS mereenfant_1
- FROM
- tracking_motherbasicinfo
- LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient
- LEFT JOIN testing_mereenfant ON CONCAT(testing_mereenfant.mother_city_code, '/', testing_mereenfant.mother_hospital_code, '/', testing_mereenfant.mother_code) = patient_code
- WHERE
- date BETWEEN @start_date_last AND @end_date_last
- AND patient_code IS NOT NULL
- GROUP BY patient_code) me1 ON mereenfant_1 = bot.patient_code
- LEFT JOIN
- (SELECT
- patient.patient_code AS mereenfant_2
- FROM
- tracking_motherbasicinfo
- LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient
- LEFT JOIN testing_mereenfant ON CONCAT(testing_mereenfant.mother_city_code, '/', testing_mereenfant.mother_hospital_code, '/', testing_mereenfant.mother_code) = patient_code
- WHERE
- date BETWEEN @start_date AND @end_date
- AND patient_code IS NOT NULL
- GROUP BY patient_code) me2 ON mereenfant_2 = bot.patient_code
- WHERE
- (linked_to_id_patient = 0 or linked_to_id_patient is null)
- AND (club_q1 IS NOT NULL
- OR club_q2 IS NOT NULL
- OR quest_q1 IS NOT NULL
- OR quest_q2 IS NOT NULL
- OR arv_1 IS NOT NULL
- OR arv_2 IS NOT NULL
- OR odk_1 IS NOT NULL
- OR odk_2 IS NOT NULL
- OR ptme_1 IS NOT NULL
- OR ptme_2 IS NOT NULL
- OR ptme1_1 IS NOT NULL
- OR ptme1_2 IS NOT NULL
- OR mereenfant_1 IS NOT NULL
- OR mereenfant_2 IS NOT NULL)
- GROUP BY bot.patient_code
Add Comment
Please, Sign In to add comment