gistfile1.txt

_________________________________________________________M_________________________________________________________x
TX_TB NUM
_________________________________________________________W_________________________________________________________
PACIENTES COM RASTREIO DE TB (EXCLUINDO OS COM INICIO TB 6 MESES ATRAS)
> patients who started TB treatment or enrolled in TB program within reporting period but didn't start TB Treatment within 6 months before startDate


SELECT patient_id FROM
(
    -- PATIENT STARTED TB TREATMENT
    SELECT p.patient_id FROM patient p
        INNER JOIN encounter e ON e.patient_id = p.patient_id
        INNER JOIN obs o ON o.encounter_id = e.encounter_id
            WHERE p.voided = 0 AND e.voided = 0 AND o.voided = 0
            AND e.encounter_type IN (6,9)
                AND o.concept_id = 1113
                AND e.location_id = :location AND o.value_datetime between :startDate AND :endDate 
                    GROUP BY p.patient_id
    UNION

    -- PATIENTS ON TB PROGRAM
    SELECT p.patient_id FROM patient p
        INNER JOIN patient_program pg ON pg.patient_id = p.patient_id
            WHERE p.voided = 0 AND pg.voided = 0
                AND pg.program_id = 5 
                AND pg.date_enrolled BETWEEN :startDate AND :endDate AND pg.location_id = :location
                AND pg.date_completed IS NULL
                    GROUP BY p.patient_id
)tb_patients 

WHERE tb_patients.patient_id NOT IN
(
    -- PATIENT STARTED TB TREATMENT 6 MONTH AGO
    SELECT p.patient_id FROM patient p
        INNER JOIN encounter e ON e.patient_id = p.patient_id
        INNER JOIN obs o ON o.encounter_id = e.encounter_id
            WHERE p.voided = 0 AND e.voided = 0 AND o.voided = 0
            AND e.encounter_type IN (6,9)
                AND o.concept_id = 1113
                AND e.location_id = :location AND o.value_datetime BETWEEN (:startDate - INTERVAL 6 MONTH) AND (:startDate - INTERVAL 1 DAY)
                    GROUP BY p.patient_id
)

GROUP BY tb_patients.patient_id;


_________________________________________________________ _________________________________________________________
AND
_________________________________________________________ _________________________________________________________
INICIO DE TRATAMENTO ARV - PERIODO FINAL: INCLUI TRANSFERIDOS DE COM DATA DE INICIO CONHECIDA (SQL)
> on art start drugs ART plan on either pharmacy or ped or adult return encounters
> or historical drug start date on either pharmacy or ped or adult return encounters
> or enrolled into ART on of before endDate
> or had a pharmacy encounter before or on endDate

Select 	p.patient_id
from 	patient p 
		inner join encounter e on p.patient_id=e.patient_id	
		inner join obs o on o.encounter_id=e.encounter_id
where 	e.voided=0 and o.voided=0 and p.voided=0 and 
		e.encounter_type in (18,6,9) and o.concept_id=1255 and o.value_coded=1256 and 
		e.encounter_datetime<=:endDate and e.location_id=:location
group by p.patient_id
	
union

Select 	p.patient_id
from 	patient p
		inner join encounter e on p.patient_id=e.patient_id
		inner join obs o on e.encounter_id=o.encounter_id
where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type in (18,6,9) and 
		o.concept_id=1190 and o.value_datetime is not null and 
		o.value_datetime<=:endDate and e.location_id=:location
group by p.patient_id

union

select 	pg.patient_id
from 	patient p inner join patient_program pg on p.patient_id=pg.patient_id
where 	pg.voided=0 and p.voided=0 and program_id=2 and date_enrolled<=:endDate and location_id=:location

union

SELECT 	p.patient_id 
FROM 	patient p
		inner join encounter e on p.patient_id=e.patient_id
WHERE	p.voided=0 and e.encounter_type=18 AND e.voided=0 and e.encounter_datetime<=:endDate and e.location_id=:location
GROUP BY 	p.patient_id


_________________________________________________________M_________________________________________________________

TX_TB DEN
_________________________________________________________W_________________________________________________________
INICIO DE TRATAMENTO ARV - PERIODO FINAL: INCLUI TRANSFERIDOS DE COM DATA DE INICIO CONHECIDA (SQL)
> 

Select 	p.patient_id
from 	patient p 
		inner join encounter e on p.patient_id=e.patient_id	
		inner join obs o on o.encounter_id=e.encounter_id
where 	e.voided=0 and o.voided=0 and p.voided=0 and 
		e.encounter_type in (18,6,9) and o.concept_id=1255 and o.value_coded=1256 and 
		e.encounter_datetime<=:endDate and e.location_id=:location
group by p.patient_id
	
union

Select 	p.patient_id
from 	patient p
		inner join encounter e on p.patient_id=e.patient_id
		inner join obs o on e.encounter_id=o.encounter_id
where 	p.voided=0 and e.voided=0 and o.voided=0 and e.encounter_type in (18,6,9) and 
		o.concept_id=1190 and o.value_datetime is not null and 
		o.value_datetime<=:endDate and e.location_id=:location
group by p.patient_id

union

select 	pg.patient_id
from 	patient p inner join patient_program pg on p.patient_id=pg.patient_id
where 	pg.voided=0 and p.voided=0 and program_id=2 and date_enrolled<=:endDate and location_id=:location

union

SELECT 	p.patient_id 
FROM 	patient p
		inner join encounter e on p.patient_id=e.patient_id
WHERE	p.voided=0 and e.encounter_type=18 AND e.voided=0 and e.encounter_datetime<=:endDate and e.location_id=:location
GROUP BY 	p.patient_id


_________________________________________________________ _________________________________________________________
AND
_________________________________________________________ _________________________________________________________
PACIENTES COM RASTREIO DE TB (EXCLUINDO OS TRANSFERIDOS PARA SEM INICIO DE TB E COM INICIO TB 6 MESES ATRAS)


SELECT patient_id FROM
(
    -- PATIENTS SELECTED FOR TB SCREENING
    SELECT p.patient_id FROM patient p
        INNER JOIN encounter e ON e.patient_id = p.patient_id
        INNER JOIN obs o ON o.encounter_id = e.encounter_id
            WHERE p.voided = 0 AND e.voided = 0 AND o.voided = 0
            AND e.encounter_type IN (6,9)
                AND o.concept_id = 6257
                AND (o.value_coded = 1065 OR o.value_coded = 1066)
                AND e.location_id = :location AND e.encounter_datetime between :startDate AND :endDate 
                    GROUP BY p.patient_id
    UNION

    -- PATIENTS SELECTED FOR RESULTADO DA INVESTIGAÇÃO PARA TB DE BK E/OU RX?
    SELECT p.patient_id FROM patient p
        INNER JOIN encounter e ON e.patient_id = p.patient_id
        INNER JOIN obs o ON o.encounter_id = e.encounter_id
            WHERE p.voided = 0 AND e.voided = 0 AND o.voided = 0
            AND e.encounter_type IN (6,9)
                AND o.concept_id = 6277
                AND (o.value_coded = 703 OR o.value_coded = 664)
                AND e.location_id = :location AND e.encounter_datetime between :startDate AND :endDate 
                    GROUP BY p.patient_id
    UNION

    -- PATIENT STARTED TB TREATMENT
    SELECT p.patient_id FROM patient p
        INNER JOIN encounter e ON e.patient_id = p.patient_id
        INNER JOIN obs o ON o.encounter_id = e.encounter_id
            WHERE p.voided = 0 AND e.voided = 0 AND o.voided = 0
            AND e.encounter_type IN (6,9)
                AND o.concept_id = 1113
                AND e.location_id = :location AND o.value_datetime between :startDate AND :endDate 
                    GROUP BY p.patient_id
    UNION

    -- PATIENTS ON TB PROGRAM
    SELECT p.patient_id FROM patient p
        INNER JOIN patient_program pg ON pg.patient_id = p.patient_id
            WHERE p.voided = 0 AND pg.voided = 0
                AND pg.program_id = 5 
                AND pg.date_enrolled BETWEEN :startDate AND :endDate AND pg.location_id = :location
                AND pg.date_completed IS NULL
                    GROUP BY p.patient_id
)tb_patients 

WHERE tb_patients.patient_id NOT IN
(
    -- PATIENTS TRANSFERED OUT BUT NOT STARTED TB
    SELECT p.patient_id FROM patient p
	    INNER JOIN patient_program pg ON pg.patient_id = p.patient_id
	    INNER JOIN patient_state ps ON ps.patient_program_id = pg.patient_program_id
	        WHERE p.voided = 0 AND pg.voided = 0 AND ps.voided = 0
	            AND pg.program_id = 2
	            AND ps.state = 7
                AND ps.start_date BETWEEN :startDate AND :endDate
                AND ps.end_date IS NULL
                AND pg.location_id = :location
                AND p.patient_id NOT IN
                (
                     -- PATIENT STARTED TB TREATMENT
                    SELECT p.patient_id FROM patient p
                        INNER JOIN encounter e ON e.patient_id = p.patient_id
                        INNER JOIN obs o ON o.encounter_id = e.encounter_id
                            WHERE p.voided = 0 AND e.voided = 0 AND o.voided = 0
                            AND e.encounter_type IN (6,9)
                                AND o.concept_id = 1113
                                AND e.location_id = :location AND o.value_datetime between :startDate AND :endDate 
                                    GROUP BY p.patient_id
                    UNION

                    -- PATIENTS ON TB PROGRAM
                    SELECT p.patient_id FROM patient p
                        INNER JOIN patient_program pg ON pg.patient_id = p.patient_id
                            WHERE p.voided = 0 AND pg.voided = 0
                                AND pg.program_id = 5 
                                AND pg.date_enrolled BETWEEN :startDate AND :endDate AND pg.location_id = :location
                                AND pg.date_completed IS NULL
                                    GROUP BY p.patient_id
                                    
                ) GROUP BY p.patient_id
    UNION

    -- PATIENT STARTED TB TREATMENT 6 MONTH AGO
    SELECT p.patient_id FROM patient p
        INNER JOIN encounter e ON e.patient_id = p.patient_id
        INNER JOIN obs o ON o.encounter_id = e.encounter_id
            WHERE p.voided = 0 AND e.voided = 0 AND o.voided = 0
            AND e.encounter_type IN (6,9)
                AND o.concept_id = 1113
                AND e.location_id = :location AND o.value_datetime BETWEEN (:startDate - INTERVAL 6 MONTH) AND (:startDate - INTERVAL 1 DAY)
                    GROUP BY p.patient_id
    UNION

    -- PATIENTS ON TB PROGRAM 6 MONTH AGO
    SELECT p.patient_id FROM patient p
        INNER JOIN patient_program pg ON pg.patient_id = p.patient_id
            WHERE p.voided = 0 AND pg.voided = 0
                AND pg.program_id = 5 
                AND pg.date_enrolled BETWEEN (:startDate - INTERVAL 6 MONTH) AND (:startDate - INTERVAL 1 DAY) AND pg.location_id = :location
                AND pg.date_completed IS NULL
                    GROUP BY p.patient_id
)

GROUP BY tb_patients.patient_id;