Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement