PMTCT_EID newest

USE caris_db;
SET @start_date="2015-03-01";
SET @end_date="2015-09-30";
###PMTCT_EID
SELECT
hospital_code,
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.hospital_code), 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 hospital_code , hospital_code AS hcode_nocity FROM lookup_hospital) a LEFT JOIN
(

SELECT COUNT(*) AS 'numb_positive_pregnant_women' , CONCAT(city_code, "/", hospital_code) AS hospital_code 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 hospital_code
) abc00 ON abc00.hospital_code=a.hospital_code
### INFANT VIROLOGY
#WITHING 2 MONTHS
LEFT JOIN (
SELECT COUNT(*) AS `virologic_test_within_2_months_of_birth`, CONCAT(city_code, "/", hospital_code) AS hospital_code
 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 hospital_code
) abc01 ON abc01.hospital_code=a.hospital_code
# BETWEEN 0 12
LEFT JOIN (
SELECT COUNT(*) AS "virologic_HIV_test_within_12_months", CONCAT(city_code, "/", hospital_code) AS hospital_code
 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 hospital_code
) abc02 ON abc02.hospital_code=a.hospital_code
#POSITIVE WITHIN 2 MONTHS
LEFT JOIN (
SELECT COUNT(*) AS "positive_virologic_test_within_2_months", CONCAT(city_code, "/", hospital_code) AS hospital_code
 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 hospital_code
) abc03 ON abc03.hospital_code=a.hospital_code

# POSITIVE BETWEEN 0 12
LEFT JOIN (
SELECT COUNT(*) AS "positive_virologic_test_within_12_months", CONCAT(city_code, "/", hospital_code) AS hospital_code
 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 hospital_code
) abc04 ON abc04.hospital_code=a.hospital_code


) b

) c
ORDER BY c.hospital_code