PMTCT_EID

USE caris_db;
SET @start_date="2015-03-01";
SET @end_date="2015-09-30";

SELECT DISTINCT(a.hospital_code),  count_patient_whose_pcr_done, count_patient_virological_test_2_month, infant_virological_test_btw_2and12 ,  positive_pcr_within12, positive_pcr_within_2, positive_pcr_btw_2_12, positive_pregnant_women
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code , hospital_code AS hcode_nocity FROM lookup_hospital) a LEFT JOIN
(
###PMTCT_EID

##NUMERATOR: NUMBER OF CHILDREN WE TESTED AT EACH SITE SINCE 10/1/13

#SELECT * FROM testing_specimen
#SELECT count(*) FROM testing_specimen
#SELECT * FROM testing_result
#SELECT count(*) FROM testing_result

#total number of tests performed
#select id_patient from testing_specimen where date_blood_taken >= "2013-10-01"
#and date_blood_taken <= "2014-09-31" limit 500000 #returns 3459 rows

SELECT abc12.hospital_code AS "hospital_code", infant_virological_test_btw_2and12 ,  positive_pcr_within12, positive_pcr_within_2, positive_pcr_btw_2_12, count_patient_whose_pcr_done, count_patient_virological_test_2_month, positive_pregnant_women  FROM
(



SELECT abc1.hospital_code AS "hospital_code",  count_patient_whose_pcr_done, count_patient_virological_test_2_month   FROM (
#by site

SELECT lookup_hospital.hospital_code, count_patient_whose_pcr_done
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code FROM lookup_hospital) lookup_hospital LEFT JOIN (
SELECT count(*) AS "count_patient_whose_pcr_done" , hospital_code
FROM (
SELECT * FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code, id FROM patient) patient
WHERE
id IN (SELECT id_patient FROM testing_specimen WHERE date_blood_taken >= @start_date
AND date_blood_taken <= @end_date)
) a
GROUP BY hospital_code
ORDER BY hospital_code
)
x1
ON x1.hospital_code = lookup_hospital.hospital_code

) abc1,  (

##DENOMINATOR: NUMBER OF WOMEN WHO ARE POSITIVE AND PREGNANT AT EACH SITE

#see PMP code-2 for clarification (iSante and MESI)

##DISAGGREGATION

##infants who received a virologic test within 2 months of birth
SELECT lookup_hospital.hospital_code, count_patient_virological_test_2_month
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code FROM lookup_hospital)
lookup_hospital LEFT JOIN (
SELECT count(*) AS "count_patient_virological_test_2_month", hospital_code
FROM (
SELECT * FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code, id FROM patient) 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
) #and which_pcr = 2

) d
GROUP BY hospital_code
ORDER BY hospital_code
)
x2
ON x2.hospital_code = lookup_hospital.hospital_code



) abc2
WHERE abc1.hospital_code=abc2.hospital_code


) abc12
,
(

SELECT abc3.hospital_code AS "hospital_code" ,  infant_virological_test_btw_2and12 ,  positive_pcr_within12, positive_pcr_within_2, positive_pcr_btw_2_12, positive_pregnant_women FROM (
##infants who received a virologic test between 2 months and 12 months of birth, DATEDIFF
#this should be the difference the numerator and the first disaggregation described above

SELECT lookup_hospital.hospital_code, infant_virological_test_btw_2and12
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code FROM lookup_hospital)
lookup_hospital LEFT JOIN (
SELECT count(*) AS "infant_virological_test_btw_2and12", hospital_code
FROM (
SELECT * FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code, id FROM patient) 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) >= 62
AND DATEDIFF(date_blood_taken,date_of_birth) <= 365 #and which_pcr = 1
)  #and which_pcr = 2
AND # make sure patient ids counted in less than 2 months is also counted in 2 and 12 (in case they had 2nd PCR later
id NOT 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
) #and which_pcr = 2


) bbb
GROUP BY hospital_code
ORDER BY hospital_code
) x3
ON x3.hospital_code = lookup_hospital.hospital_code
) abc3 ,

##infants with a positive pcr within 12 months of birth
(
SELECT lookup_hospital.hospital_code, positive_pcr_within12
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code FROM lookup_hospital)
lookup_hospital LEFT JOIN (
SELECT count(*) AS "positive_pcr_within12" , hospital_code
FROM (
SELECT * FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code, id FROM patient) 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) >= 1
AND DATEDIFF(date_blood_taken,date_of_birth) <= 365
AND pcr_result = 1 #and which_pcr = 1
) #and which_pcr = 2
) jj #returns 5 rows
GROUP BY hospital_code
ORDER BY hospital_code
)x4
ON x4.hospital_code = lookup_hospital.hospital_code

)abc4
,
(
SELECT lookup_hospital.hospital_code, positive_pregnant_women
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code FROM lookup_hospital)
lookup_hospital
LEFT JOIN (
SELECT count(*) AS "positive_pregnant_women" , hospital_code
FROM (
SELECT * FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code, id FROM patient) 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)
)
) jj5
GROUP BY hospital_code
ORDER BY hospital_code
)x5
ON x5.hospital_code = lookup_hospital.hospital_code

)abc5
,

(
SELECT lookup_hospital.hospital_code, positive_pcr_within_2
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code FROM lookup_hospital)
lookup_hospital LEFT JOIN (
SELECT count(*) AS "positive_pcr_within_2" , hospital_code
FROM (
SELECT * FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code, id FROM patient) 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) >= 1
AND DATEDIFF(date_blood_taken,date_of_birth) <= 60
AND pcr_result = 1 #and which_pcr = 1
) #and which_pcr = 2
) jj0 #returns 5 rows
GROUP BY hospital_code
ORDER BY hospital_code
)x41
ON x41.hospital_code = lookup_hospital.hospital_code

)abc6
,
(
SELECT lookup_hospital.hospital_code, positive_pcr_btw_2_12
FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code FROM lookup_hospital)
lookup_hospital LEFT JOIN (
SELECT count(*) AS "positive_pcr_btw_2_12" , hospital_code
FROM (
SELECT * FROM (SELECT CONCAT(city_code, "/", hospital_code) AS hospital_code, id FROM patient) 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) > 60
AND DATEDIFF(date_blood_taken,date_of_birth) <= 365
AND pcr_result = 1 #and which_pcr = 1
) #and which_pcr = 2
AND
id not 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) >= 1
AND DATEDIFF(date_blood_taken,date_of_birth) <= 60
AND pcr_result = 1 #and which_pcr = 1
)

) jj1 #returns 5 rows
GROUP BY hospital_code
ORDER BY hospital_code
)x42
ON x42.hospital_code = lookup_hospital.hospital_code

)abc7

WHERE abc3.hospital_code=abc4.hospital_code and abc4.hospital_code = abc5.hospital_code and abc4.hospital_code = abc6.hospital_code and abc7.hospital_code = abc6.hospital_code


) abc34

WHERE abc34.hospital_code = abc12.hospital_code
) b
ON a.hospital_code = b.hospital_code

ORDER BY hcode_nocity