Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE caris_db;
- SET
- @start_date = "2019-01-01";
- SET
- @end_date = "2019-03-31";
- SELECT
- 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,
- IF(regime.id_patient IS NOT NULL,
- 'yes',
- 'non') AS on_arv,
- office
- FROM
- (SELECT
- id_patient, MIN(date) AS date
- FROM
- (SELECT
- id_patient, date
- FROM
- (SELECT
- id_patient, date
- FROM
- (SELECT
- id_patient, date
- FROM
- questionnaire_motherhivknowledge
- WHERE
- date IS NOT NULL
- AND date != '0000-00-00' UNION SELECT
- id_patient, date
- FROM
- questionnaire_mothersurvey
- WHERE
- date IS NOT NULL
- AND date != '0000-00-00' UNION SELECT
- id_patient, date
- FROM
- questionnaire_newmotherhivknowledge
- WHERE
- date IS NOT NULL
- AND date != '0000-00-00') quest
- LEFT JOIN patient ON patient.id = quest.id_patient
- WHERE
- patient.created_at >= @start_date) ques UNION SELECT
- id_patient, date
- FROM
- (SELECT
- patient.id AS id_patient, date_of_visit AS date
- FROM
- patient
- LEFT JOIN openfn.odk_pregnancy_visit ON patient.patient_code = health_id
- WHERE
- date_of_visit IS NOT NULL
- AND date_of_visit != '0000-00-00'
- AND patient.created_at >= @start_date) odk UNION SELECT
- id_patient, date
- FROM
- (SELECT
- id_patient, date
- FROM
- session
- LEFT JOIN club_session ON club_session.id = session.id_club_session
- LEFT JOIN club ON club.id = id_club
- LEFT JOIN patient ON patient.id = id_patient
- WHERE
- is_present = 1 AND club_type = 1
- AND patient.created_at >= @start_date) club UNION SELECT
- mereenfant.id AS id_patient, date
- FROM
- (SELECT
- patient.id, date
- FROM
- testing_mereenfant
- LEFT JOIN patient ON patient.patient_code = CONCAT(city_code, '/', hospital_code, '/', mother_code)
- WHERE
- patient.id IS NOT NULL
- AND patient.created_at >= @start_date) mereenfant) last
- GROUP BY id_patient) lastone
- LEFT JOIN
- patient ON patient.id = lastone.id_patient
- LEFT JOIN
- tracking_motherbasicinfo ON tracking_motherbasicinfo.id_patient = lastone.id_patient
- LEFT JOIN
- lookup_hospital ON CONCAT(lookup_hospital.city_code,
- '/',
- lookup_hospital.hospital_code) = LEFT(patient_code, 8)
- LEFT JOIN
- (SELECT
- distinct id_patient
- FROM
- tracking_regime
- WHERE
- category = 'regime_mother_treatment') regime ON regime.id_patient = lastone.id_patient
- WHERE
- date BETWEEN @start_date AND @end_date
- AND patient.created_at >= @start_date
- AND lastone.id_patient IS NOT NULL
- AND tracking_motherbasicinfo.id_patient IS NOT NULL
Add Comment
Please, Sign In to add comment