Closed nupoorkhandelwal closed 3 months ago
select *
from program_encounter
where encounter_type_id in (
select id
from encounter_type
where is_voided = false
and name != 'Annual Visit - Baseline'
and name != 'Annual Visit - Endline'
)
-- and extract(years from earliest_visit_date_time) >= 2023
-- and earliest_visit_date_time >= '2023-04-01 00:00:00'
and earliest_visit_date_time >= '2024-03-31 23:59:59'
and individual_id in (
select distinct program_encounter.individual_id
from program_encounter
join address_level on program_encounter.address_id = address_level.id
join address_level_type on address_level.type_id = address_level_type.id
join program_enrolment on program_encounter.program_enrolment_id = program_enrolment.id and program_exit_date_time is null
where
encounter_type_id = (select id from encounter_type where is_voided = false and name = 'Annual Visit - Baseline')
and extract(years from earliest_visit_date_time) >= 2022
and program_encounter.observations @> '{"2424293e-2466-4122-970f-716f3019ad55":"910536c2-5f03-4206-b08c-5a0d0d140d78"}'::jsonb
and address_level_type.name != 'Village'
)
and encounter_date_time is null
and cancel_date_time is null;
except
select distinct individual_id
from program_encounter
where encounter_type_id in (
select id
from encounter_type
where is_voided = false
and name != 'Annual Visit - Baseline'
and name != 'Annual Visit - Endline'
)
and earliest_visit_date_time >= '2023-04-01 00:00:00'
and earliest_visit_date_time <= '2024-03-31 23:59:59'
and encounter_date_time is null
and cancel_date_time is null;
@Dinesh2019 this has been done on Pre release env - ADSR org
[ ] 3. Update Query. set ->>