USE caris_db;
SET
@start_date = "2019-01-01";
SET
@end_date = "2019-03-31";
SELECT
patient_code,
COALESCE(ti.dob, tm.infant_dob, ts.date_of_birth) AS dob,
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 1
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00',
'under1',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 1
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 10
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00',
'bt_1_9',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 1
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 1 OR tm.infant_gender = 1),
'm_under1',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 1
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 2 OR tm.infant_gender = 2),
'f_under1',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 1
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 10
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 1 OR tm.infant_gender = 1),
'm_bt_1_9',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 1
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 10
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 2 OR tm.infant_gender = 2),
'f_bt_1_9',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 10
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 15
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 1 OR tm.infant_gender = 1),
'm_bt_10_14',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 10
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 15
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 2 OR tm.infant_gender = 2),
'f_bt_10_14',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 15
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 18
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 1 OR tm.infant_gender = 1),
'm_bt_15_17',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 15
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 18
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 2 OR tm.infant_gender = 2),
'f_bt_15_17',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 18
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 25
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 1 OR tm.infant_gender = 1),
'm_bt_18_24',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 18
AND TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) < 25
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 2 OR tm.infant_gender = 2),
'f_bt_18_24',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 25
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 1 OR tm.infant_gender = 1),
'm_25',
IF(TIMESTAMPDIFF(YEAR,
COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob),
@start_date) >= 25
AND COALESCE(IF(tm.infant_dob = '0000-00-00',
NULL,
tm.infant_dob),
IF(ts.date_of_birth = '0000-00-00',
NULL,
ts.date_of_birth),
ti.dob) != '0000-00-00'
AND (ti.gender = 2 OR tm.infant_gender = 2),
'f_25',
'')))))))))))))) AS tranche,
IF(regime.id_patient IS NOT NULL,
'yes',
'non') AS on_arv,
office,
IF(statut.id_patient IS NOT NULL,
'pos',
'neg') AS statut
FROM
(SELECT
id_patient, MIN(date) AS date
FROM
(SELECT
id_patient, date
FROM
(SELECT
id_patient, date
FROM
questionnaire_child
LEFT JOIN patient ON patient.id = id_patient
WHERE
patient.created_at >= @start_date
AND date IS NOT NULL
AND date != '0000-00-00') ques UNION SELECT
id_patient, date
FROM
(SELECT
patient.id AS id_patient, date_of_visit AS date
FROM
patient
LEFT JOIN openfn.odk_child_visit od ON patient.patient_code = od.patient_code
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
id_patient, date
FROM
(SELECT
id_patient, date
FROM
(SELECT
id_patient, date
FROM
testing_mereenfant UNION SELECT
id_patient, date_blood_taken AS date
FROM
testing_specimen UNION SELECT
id_patient, blood_draw_date AS date
FROM
testing_result) pcr
LEFT JOIN patient ON patient.id = pcr.id_patient
WHERE
patient.created_at >= @start_date) p UNION SELECT
id_patient, date
FROM
(SELECT
id_patient, date
FROM
tracking_followup
LEFT JOIN patient ON patient.id = id_patient
WHERE
patient.created_at >= @start_date) o) last
GROUP BY id_patient) lastone
LEFT JOIN
patient ON patient.id = lastone.id_patient
LEFT JOIN
tracking_infant ti ON ti.id_patient = lastone.id_patient
LEFT JOIN
testing_mereenfant tm ON lastone.id_patient = tm.id_patient
LEFT JOIN
testing_specimen ts ON ts.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_infant_treatment') regime ON regime.id_patient = lastone.id_patient
LEFT JOIN
(SELECT
id AS id_patient
FROM
view_patient_positive UNION SELECT
id_patient
FROM
tracking_regime
WHERE
category = 'regime_infant_treatment') statut ON statut.id_patient = lastone.id_patient
WHERE
lastone.date BETWEEN @start_date AND @end_date
AND patient.created_at >= @start_date
AND lastone.id_patient IS NOT NULL
AND linked_to_id_patient = 0
GROUP BY lastone.id_patient
Comments