Pasted

USE caris_db;
SET @start_date="2016-07-01";
SET @end_date="2016-09-31";
###PMTCT_EID
SELECT 
hc,
virologic_HIV_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,
negative_virologic_test_within_2_months,
(negative_virologic_test_within_12_months-negative_virologic_test_within_2_months) as negative_virologic_test_bet_2_and_12_months,
(virologic_test_within_2_months_of_birth-positive_virologic_test_within_2_months-negative_virologic_test_within_2_months)as no_result_within_2,
(virologic_HIV_test_within_12_months-virologic_test_within_2_months_of_birth-positive_virologic_test_within_12_months+positive_virologic_test_within_2_months-negative_virologic_test_within_12_months+negative_virologic_test_within_2_months) as no_result_2_12
 
FROM (
 
SELECT b.*
  FROM(
 
SELECT DISTINCT(a.hc), 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,
IF(abc05.negative_virologic_test_within_2_months IS NULL,0,abc05.negative_virologic_test_within_2_months) as negative_virologic_test_within_2_months,
IF(abc06.negative_virologic_test_within_12_months IS NULL,0,abc06.negative_virologic_test_within_12_months) as negative_virologic_test_within_12_months
 
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hc , hospital_code AS hcode_nocity FROM lookup_hospital) a 
### 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 t1 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 date_blood_taken=(select min(date_blood_taken) from testing_specimen t2 where t1.id_patient=t2.id_patient) #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 t1 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 date_blood_taken=(select min(date_blood_taken) from testing_specimen t2 where t1.id_patient=t2.id_patient) #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 t1 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 date_blood_taken=(select min(date_blood_taken) from testing_specimen t2 where t1.id_patient=t2.id_patient) #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 t1 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 date_blood_taken=(select min(date_blood_taken) from testing_specimen t2 where t1.id_patient=t2.id_patient) #which_pcr = 1
)
GROUP BY hc
) abc04 on abc04.hc=a.hc
 
 
 
#NEGATIVE WITHIN 2 MONTHS
LEFT JOIN (
SELECT count(*) AS "negative_virologic_test_within_2_months", CONCAT(city_code, "/", hospital_code) AS hc
 from patient WHERE id IN (SELECT id_patient FROM testing_specimen t1 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=2 AND date_blood_taken=(select min(date_blood_taken) from testing_specimen t2 where t1.id_patient=t2.id_patient) #which_pcr = 1
) GROUP BY hc
) abc05 on abc05.hc=a.hc
 
# NEGATIVE BETWEEN 0 12
LEFT JOIN (
SELECT count(*) AS "negative_virologic_test_within_12_months", CONCAT(city_code, "/", hospital_code) AS hc
 from patient
WHERE 
id IN (SELECT id_patient FROM testing_specimen t1 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 = 2 AND date_blood_taken=(select min(date_blood_taken) from testing_specimen t2 where t1.id_patient=t2.id_patient) #which_pcr = 1
)
GROUP BY hc
) abc06 on abc06.hc=a.hc
 
 
 
) b
 
) c
ORDER BY c.hc