PMTCT EID reviewed

USE caris_db;
SET @start_date="2016-07-01";
SET @end_date="2016-09-31";
###PMTCT_EID
SELECT
hc,
numb_positive_pregnant_women,
virologic_HIV_test_within_12_months,
virologic_test_within_2_months_of_birth,
(virologic_HIV_test_within_12_months-virologic_test_within_2_months_of_birth) as virologic_HIV_test_bet_2_and_12_months,
positive_virologic_test_within_12_months,
positive_virologic_test_within_2_months,
(positive_virologic_test_within_12_months-positive_virologic_test_within_2_months) as positive_virologic_test_bet_2_and_12_months
FROM (

SELECT b.*
  FROM(

SELECT DISTINCT(a.hc), IF(abc00.numb_positive_pregnant_women IS NULL,0,abc00.numb_positive_pregnant_women) as numb_positive_pregnant_women,
IF(abc01.virologic_test_within_2_months_of_birth IS NULL,0,abc01.virologic_test_within_2_months_of_birth) as virologic_test_within_2_months_of_birth,
IF(abc02.virologic_HIV_test_within_12_months IS NULL,0,abc02.virologic_HIV_test_within_12_months) as virologic_HIV_test_within_12_months,
IF(abc03.positive_virologic_test_within_2_months IS NULL,0,abc03.positive_virologic_test_within_2_months) as positive_virologic_test_within_2_months,
IF(abc04.positive_virologic_test_within_12_months IS NULL,0,abc04.positive_virologic_test_within_12_months) as positive_virologic_test_within_12_months

FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hc , hospital_code AS hcode_nocity FROM lookup_hospital) a LEFT JOIN
(

SELECT count(*) AS 'numb_positive_pregnant_women' , CONCAT(city_code, "/", hospital_code) AS hc from patient
WHERE
id IN (
SELECT id_patient_mother FROM tracking_pregnancy where (dpa between (@start_date) and (@end_date + interval 9 month)
or
ddr between (@start_date - interval 9 month) and (@end_date -  interval 2 week))
 )
GROUP BY hc
) abc00 ON abc00.hc=a.hc
### INFANT VIROLOGY
#WITHING 2 MONTHS
LEFT JOIN (
SELECT count(*) AS `virologic_test_within_2_months_of_birth`, CONCAT(city_code, "/", hospital_code) AS hc
 from patient
WHERE
id IN (SELECT id_patient FROM testing_specimen WHERE date_blood_taken >= @start_date
AND date_blood_taken <= @end_date AND DATEDIFF(date_blood_taken,date_of_birth) <= 61
AND DATEDIFF(date_blood_taken,date_of_birth) > 0 AND which_pcr = 1
) GROUP BY hc
) abc01 on abc01.hc=a.hc
# BETWEEN 0 12
LEFT JOIN (
SELECT count(*) AS "virologic_HIV_test_within_12_months", CONCAT(city_code, "/", hospital_code) AS hc
 from patient
WHERE
id IN (SELECT id_patient FROM testing_specimen WHERE date_blood_taken >= @start_date
AND date_blood_taken <= @end_date AND DATEDIFF(date_blood_taken,date_of_birth) >= 0
AND DATEDIFF(date_blood_taken,date_of_birth) <= 365 AND which_pcr = 1
)
GROUP BY hc
) abc02 on abc02.hc=a.hc
#POSITIVE WITHIN 2 MONTHS
LEFT JOIN (
SELECT count(*) AS "positive_virologic_test_within_2_months", CONCAT(city_code, "/", hospital_code) AS hc
 from patient WHERE id IN (SELECT id_patient FROM testing_specimen WHERE date_blood_taken >= @start_date
AND date_blood_taken <= @end_date AND DATEDIFF(date_blood_taken,date_of_birth) <= 61
AND DATEDIFF(date_blood_taken,date_of_birth) > 0 AND pcr_result=1 and which_pcr = 1
) GROUP BY hc
) abc03 on abc03.hc=a.hc

# POSITIVE BETWEEN 0 12
LEFT JOIN (
SELECT count(*) AS "positive_virologic_test_within_12_months", CONCAT(city_code, "/", hospital_code) AS hc
 from patient
WHERE
id IN (SELECT id_patient FROM testing_specimen WHERE date_blood_taken >= @start_date
AND date_blood_taken <= @end_date AND DATEDIFF(date_blood_taken,date_of_birth) >= 0
AND DATEDIFF(date_blood_taken,date_of_birth) <= 365 and pcr_result = 1 and which_pcr = 1
)
GROUP BY hc
) abc04 on abc04.hc=a.hc


) b

) c
ORDER BY c.hc