Pasted

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