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
patient_code,
IF(arv.id_patient IS NOT NULL,
'yes',
'no') AS on_arv,
IF(statut.id_patient IS NOT NULL,
'pos',
'neg') AS statut,
IF(followup_q1 IS NOT NULL
OR odk_q1 IS NOT NULL
OR arv_q1 IS NOT NULL
OR ques_q1 IS NOT NULL
OR club_q1 IS NOT NULL
OR mereenfant_q1 is not null
OR specimen_q1 is not null,
'yes',
'no') AS q1,
IF(followup_q1 IS NOT NULL, 'yes', 'no') AS followup_q1,
IF(odk_q1 IS NOT NULL, 'yes', 'no') AS odk_q1,
IF(arv_q1 IS NOT NULL, 'yes', 'no') AS arv_q1,
IF(ques_q1 IS NOT NULL, 'yes', 'no') AS ques_q1,
IF(club_q1 IS NOT NULL, 'yes', 'no') AS club_q1,
if(mereenfant_q1 is not null, 'yes','no') as mereenfant_q1,
if(specimen_q1 is not null, 'yes','no') as specimen_q1,
IF(followup_q2 IS NOT NULL
OR odk_q2 IS NOT NULL
OR arv_q2 IS NOT NULL
OR ques_q2 IS NOT NULL
OR club_q2 IS NOT NULL
or mereenfant_q2 is not null
or specimen_q2 is not null,
'yes',
'no') AS q2,
IF(followup_q2 IS NOT NULL, 'yes', 'no') AS followup_q2,
IF(odk_q2 IS NOT NULL, 'yes', 'no') AS odk_q2,
IF(arv_q2 IS NOT NULL, 'yes', 'no') AS arv_q2,
IF(ques_q2 IS NOT NULL, 'yes', 'no') AS ques_q2,
IF(club_q2 IS NOT NULL, 'yes', 'no') AS club_q2,
if(mereenfant_q2 is not null, 'yes','no') as mereenfant_q2,
if(specimen_q2 is not null, 'yes','no') as specimen_q2,
office
FROM
(SELECT
*
FROM
(SELECT
id_patient
FROM
tracking_followup
WHERE
date BETWEEN @start_date_last AND @end_date UNION (SELECT DISTINCT
patient.id AS id_patient
FROM
openfn.odk_child_visit
LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
WHERE
date_of_visit BETWEEN @start_date_last AND @end_date
AND patient.id IS NOT NULL
AND is_available_at_time_visit = 1
AND linked_to_id_patient = 0) UNION SELECT
id_patient
FROM
tracking_regime
WHERE
(start_date BETWEEN @start_date_last AND @end_date or end_date BETWEEN @start_date_last AND @end_date)
AND category = 'regime_infant_treatment' UNION SELECT
id_patient
FROM
questionnaire_child
WHERE
date BETWEEN @start_date_last AND @end_date UNION SELECT
id_patient
FROM
testing_mereenfant
WHERE
date BETWEEN @start_date_last AND @end_date UNION SELECT
id_patient
FROM
testing_specimen
WHERE
date_blood_taken BETWEEN @start_date_last AND @end_date UNION SELECT
id_patient
FROM
questionnaire_proftraining18
WHERE
date BETWEEN @start_date_last AND @end_date UNION ((SELECT
id_patient
FROM
session
LEFT JOIN club_session ON club_session.id = id_club_session
LEFT JOIN club ON club_session.id_club = club.id
WHERE
is_present = 1
AND (date BETWEEN @start_date AND @end_date
OR date BETWEEN @start_date_last AND @end_date_last)
AND club_type != 1))) o) bot
LEFT JOIN
(SELECT DISTINCT
id_patient AS followup_q1
FROM
tracking_followup
WHERE
date BETWEEN @start_date_last AND @end_date_last) x ON followup_q1 = bot.id_patient
left join
(select distinct id_patient as specimen_q1 from testing_specimen WHERE
date_blood_taken BETWEEN @start_date_last AND @end_date_last) x2 ON specimen_q1 = bot.id_patient
left join
(select distinct id_patient as specimen_q2 from testing_specimen WHERE
date_blood_taken BETWEEN @start_date AND @end_date) x3 ON specimen_q2 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
id_patient AS followup_q2
FROM
tracking_followup
WHERE
date BETWEEN @start_date AND @end_date) y ON followup_q2 = bot.id_patient
LEFT JOIN
patient ON patient.id = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
id_patient AS mereenfant_q1
FROM
testing_mereenfant
WHERE
date BETWEEN @start_date_last AND @end_date_last) me ON mereenfant_q1 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
id_patient AS mereenfant_q2
FROM
testing_mereenfant
WHERE
date BETWEEN @start_date AND @end_date) me2 ON mereenfant_q2 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
patient.id AS odk_q1
FROM
openfn.odk_child_visit
LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
WHERE
date_of_visit BETWEEN @start_date_last AND @end_date_last
AND patient.id IS NOT NULL
AND is_available_at_time_visit = 1
AND linked_to_id_patient = 0) z ON odk_q1 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
patient.id AS odk_q2
FROM
openfn.odk_child_visit
LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
WHERE
date_of_visit BETWEEN @start_date AND @end_date
AND patient.id IS NOT NULL
AND is_available_at_time_visit = 1
AND linked_to_id_patient = 0) zz ON odk_q2 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
id_patient AS arv_q2
FROM
tracking_regime
WHERE
start_date BETWEEN @start_date AND @end_date or end_date BETWEEN @start_date AND @end_date) a2 ON arv_q2 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
id_patient AS arv_q1
FROM
tracking_regime
WHERE
start_date BETWEEN @start_date_last AND @end_date_last or end_date BETWEEN @start_date_last AND @end_date_last) a1 ON arv_q1 = bot.id_patient
LEFT JOIN
(SELECT
id_patient AS ques_q1
FROM
questionnaire_child
WHERE
date BETWEEN @start_date_last AND @end_date_last UNION SELECT
id_patient AS ques_q1
FROM
questionnaire_proftraining18
WHERE
date BETWEEN @start_date_last AND @end_date_last) q1 ON ques_q1 = bot.id_patient
LEFT JOIN
(SELECT
id_patient AS ques_q2
FROM
questionnaire_child
WHERE
date BETWEEN @start_date AND @end_date UNION SELECT
id_patient AS ques_q2
FROM
questionnaire_proftraining18
WHERE
date BETWEEN @start_date AND @end_date) q2 ON ques_q2 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
id_patient AS club_q1
FROM
session
LEFT JOIN club_session ON club_session.id = id_club_session
LEFT JOIN club ON club_session.id_club = club.id
WHERE
is_present = 1
AND date BETWEEN @start_date_last AND @end_date_last
AND club_type != 1) c1 ON club_q1 = bot.id_patient
LEFT JOIN
(SELECT DISTINCT
id_patient AS club_q2
FROM
session
LEFT JOIN club_session ON club_session.id = id_club_session
LEFT JOIN club ON club_session.id_club = club.id
WHERE
is_present = 1
AND date BETWEEN @start_date AND @end_date
AND club_type != 1) c2 ON club_q2 = bot.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') arv ON arv.id_patient = bot.id_patient
LEFT JOIN
(SELECT
id_patient
FROM
tracking_followup UNION SELECT
id_patient
FROM
tracking_regime
WHERE
category = 'regime_infant_treatment' UNION SELECT
id_patient
FROM
club_patient UNION SELECT
id_patient
FROM
questionnaire_child UNION SELECT
id_patient
FROM
questionnaire_proftraining18 UNION SELECT
id_patient
FROM
testing_specimen
WHERE
pcr_result = 1 UNION SELECT
id_patient
FROM
testing_result
WHERE
result = 1 UNION SELECT
id_patient
FROM
tracking_infant
WHERE
positive_pcr_1 != '0000-00-00'
OR positive_pcr_2 != '0000-00-00') statut ON statut.id_patient = bot.id_patient
WHERE
linked_to_id_patient = 0
Comments