Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE caris_db;
- SET @start_date="2019-04-01";
- SET @end_date="2019-06-30";
- SELECT
- a.patient_code,
- dob,
- IF(TIMESTAMPDIFF(YEAR, dob, @start_date) >= 25,
- '25+',
- IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 25
- AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 18,
- '18-24',
- IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 18
- AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 15,
- '15 17',
- IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 15
- AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 10,
- '10 15',
- IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 10
- AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 1,
- '1 9',
- ''))))) AS tranche
- FROM
- (SELECT DISTINCT
- patient_code
- FROM
- (SELECT
- patient_code
- FROM
- tracking_motherbasicinfo
- LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient
- LEFT JOIN (SELECT DISTINCT
- id_patient
- FROM
- tracking_regime
- WHERE
- category = 'regime_mother_treatment'
- AND (start_date BETWEEN @start_date AND @end_date
- OR end_date BETWEEN @start_date AND @end_date)) z ON z.id_patient = tracking_motherbasicinfo.id_patient
- LEFT JOIN (SELECT
- id_patient
- FROM
- questionnaire_motherhivknowledge
- WHERE
- date BETWEEN @start_date AND @end_date UNION SELECT
- id_patient
- FROM
- questionnaire_newmotherhivknowledge
- WHERE
- date BETWEEN @start_date AND @end_date UNION SELECT
- id_patient
- FROM
- questionnaire_mothersurvey
- WHERE
- date BETWEEN @start_date AND @end_date) x ON x.id_patient = tracking_motherbasicinfo.id_patient
- LEFT JOIN (SELECT DISTINCT
- session.id_patient
- FROM
- session
- LEFT JOIN club_session ON club_session.id = session.id_club_session
- WHERE
- is_present = 1
- AND club_session.date BETWEEN @start_date AND @end_date) y ON y.id_patient = tracking_motherbasicinfo.id_patient
- LEFT JOIN tracking_pregnancy ON tracking_pregnancy.id_patient_mother = tracking_motherbasicinfo.id_patient
- LEFT JOIN (SELECT
- tracking_motherbasicinfo.id_patient
- FROM
- testing_mereenfant
- LEFT JOIN patient ON patient.patient_code = CONCAT(mother_city_code, '/', mother_hospital_code, '/', mother_code)
- LEFT JOIN tracking_motherbasicinfo ON tracking_motherbasicinfo.id_patient = patient.id
- WHERE
- patient.id IS NOT NULL and linked_to_id_patient = 0
- AND date BETWEEN @start_date AND @end_date) abc ON abc.id_patient = tracking_motherbasicinfo.id_patient
- WHERE
- patient.id IS NOT NULL
- AND (x.id_patient IS NOT NULL
- OR PTME_date BETWEEN @start_date AND @end_date
- OR tracking_pregnancy.actual_delivery_date BETWEEN @start_date AND @end_date
- OR tracking_pregnancy.ptme_enrollment_date BETWEEN @start_date AND @end_date
- OR y.id_patient IS NOT NULL
- OR z.id_patient IS NOT NULL
- OR abc.id_patient IS NOT NULL)) c UNION SELECT DISTINCT
- health_id AS patient_code
- FROM
- openfn.odk_pregnancy_visit
- WHERE
- date_of_visit BETWEEN @start_date AND @end_date) a
- LEFT JOIN
- patient ON patient.patient_code = a.patient_code
- LEFT JOIN
- tracking_motherbasicinfo ON tracking_motherbasicinfo.id_patient = patient.id
- where linked_to_id_patient = 0 or linked_to_id_patient is null
- GROUP BY a.patient_code
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement