Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE caris_db;
- SET #start_date for Q1
- @start_date = "2018-10-01";
- SET #end_date for Q1
- @end_date = "2018-12-31";
- SET #start_date for Q2
- @start_date_curr = "2019-01-01";
- SET #end_date for Q2
- @end_date_curr = "2019-03-31";
- select alias.*, IF(ab.id_patient is not null, "yes","no") as on_arv from
- (
- select b.* from
- (SELECT
- patient.id,
- left(patient_code, 8) AS hospital_code,
- patient_code,
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 1
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00',
- 'under1',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 1
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 10
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00',
- 'bt_1_9',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 1
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_under1',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 1
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_under1',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 1
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 10
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_1_9',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 1
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 10
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_1_9',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 10
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 15
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_10_14',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 10
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 15
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_10_14',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 15
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 18
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_15_17',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 15
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 18
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_15_17',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 18
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_18_24',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 18
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_18_24',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_25',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_25',
- ''
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- ) as tranche_age,
- if(pos.id_patient is null,'negative','positive') as status
- from
- (
- select
- *
- from
- (
- select
- testing_mereenfant.id_patient,
- infant_dob as dob,
- infant_gender as gender
- from
- testing_mereenfant
- where
- date between @start_date
- and @end_date
- union
- select
- testing_specimen.id_patient,
- date_of_birth as dob,
- gender
- from
- testing_specimen
- left join tracking_infant on testing_specimen.id_patient = tracking_infant.id_patient
- where
- date_blood_taken between @start_date
- and @end_date
- union
- select
- id_patient,
- dob,
- gender
- from
- tracking_infant
- where
- (
- positive_pcr_1 between @start_date
- and @end_date
- or positive_pcr_2 between @start_date
- and @end_date
- )
- or id_patient in (
- select
- *
- from
- (
- select
- id_patient
- from
- tracking_regime
- where
- category = 'regime_infant_treatment'
- and start_date between @start_date
- and @end_date
- union
- select
- id_patient
- from
- tracking_followup
- where
- date between @start_date
- and @end_date
- union
- select
- distinct id_patient
- from
- (
- select
- odk.patient_code,
- patient.id as id_patient
- from
- openfn.odk_child_visit as odk
- left join patient on patient.patient_code = odk.patient_code
- where
- is_available_at_time_visit = 1
- and date_of_visit between @start_date
- and @end_date
- ) e
- union
- select
- distinct session.id_patient
- from
- session
- where
- is_present = 1
- and id_club_session in (
- select
- club_session.id
- from
- club_session
- left join club on club.id = club_session.id_club
- where
- date between @start_date
- and @end_date
- and club_type != 1
- )
- ) nj
- )
- ) n
- where
- dob is not null
- and dob != '0000-00-00'
- and (
- gender = 1
- or gender = 2
- )
- and id_patient not in (
- select
- id_patient
- from
- tracking_infant
- where
- (
- is_dead = 1
- and death_date < @start_date
- )
- or (
- is_abandoned = 1
- and abandoned_date < @start_date
- )
- )
- and id_patient in (
- select
- id
- from
- patient
- where
- linked_to_id_patient = 0
- )
- group by
- id_patient
- )e
- left join patient on patient.id = e.id_patient
- left join tracking_infant ti on e.id_patient = ti.id_patient
- left join testing_mereenfant tm on e.id_patient = tm.id_patient
- left join testing_specimen ts on e.id_patient = ts.id_patient
- left join (
- select id as id_patient from view_patient_positive
- union
- select id_patient from tracking_regime where category = 'regime_infant_treatment' and start_date between @start_date and @end_date
- )pos on pos.id_patient = e.id_patient
- group by patient.id
- )a
- left join
- (SELECT
- patient.id as id_patient,
- left(patient_code, 8) AS hospital_code,
- patient_code,
- COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) as birth_date,
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr) as age_up_to_date_curr,
- IF( (ti.gender = 1 or e.gender = 1 or tm.infant_gender = 1),"male",IF ((ti.gender = 2 or e.gender = 2 or tm.infant_gender = 2),"female", "unknown")) as gender,
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 1
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00',
- 'under1',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 1
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_under1',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 1
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_under1',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 1
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 5
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_1_4',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 1
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 5
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_1_4',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 5
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 10
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_5_9',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>=5
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 10
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_5_9',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 10
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 15
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_10_14',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 10
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 15
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_10_14',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 15
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 18
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_15_17',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 15
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 18
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_15_17',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 18
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_bt_18_24',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 18
- and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_bt_18_24',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 1
- or e.gender = 1
- or tm.infant_gender = 1
- ),
- 'm_25',
- if(
- timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 25
- and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00'
- and (
- ti.gender = 2
- or e.gender = 2
- or tm.infant_gender = 2
- ),
- 'f_25',
- ''
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- )
- ) as tranche_age,
- if(pos.id_patient is null,'negative','positive') as status
- from
- (
- select
- *
- from
- (
- select
- testing_mereenfant.id_patient,
- infant_dob as dob,
- infant_gender as gender
- from
- testing_mereenfant
- where
- date between @start_date_curr
- and @end_date_curr
- union
- select
- testing_specimen.id_patient,
- date_of_birth as dob,
- gender
- from
- testing_specimen
- left join tracking_infant on testing_specimen.id_patient = tracking_infant.id_patient
- where
- date_blood_taken between @start_date_curr
- and @end_date_curr
- union
- select
- id_patient,
- dob,
- gender
- from
- tracking_infant
- where
- (
- positive_pcr_1 between @start_date_curr
- and @end_date_curr
- or positive_pcr_2 between @start_date_curr
- and @end_date_curr
- )
- or id_patient in (
- select
- *
- from
- (
- select
- id_patient
- from
- tracking_regime
- where
- category = 'regime_infant_treatment'
- and start_date between @start_date_curr
- and @end_date_curr
- union
- select
- id_patient
- from
- tracking_followup
- where
- date between @start_date_curr
- and @end_date_curr
- union
- select
- distinct id_patient
- from
- (
- select
- odk.patient_code,
- patient.id as id_patient
- from
- openfn.odk_child_visit as odk
- left join patient on patient.patient_code = odk.patient_code
- where
- is_available_at_time_visit = 1
- and date_of_visit between @start_date_curr
- and @end_date_curr
- ) e
- union
- select
- distinct session.id_patient
- from
- session
- where
- is_present = 1
- and id_club_session in (
- select
- club_session.id
- from
- club_session
- left join club on club.id = club_session.id_club
- where
- date between @start_date_curr
- and @end_date_curr
- and club_type != 1
- )
- ) nj
- )
- ) n
- where
- dob is not null
- and dob != '0000-00-00'
- and (
- gender = 1
- or gender = 2
- )
- and id_patient not in (
- select
- id_patient
- from
- tracking_infant
- where
- (
- is_dead = 1
- and death_date < @start_date_curr
- )
- or (
- is_abandoned = 1
- and abandoned_date < @start_date_curr
- )
- )
- and id_patient in (
- select
- id
- from
- patient
- where
- linked_to_id_patient = 0
- )
- group by
- id_patient
- )e
- left join patient on patient.id = e.id_patient
- left join tracking_infant ti on e.id_patient = ti.id_patient
- left join testing_mereenfant tm on e.id_patient = tm.id_patient
- left join testing_specimen ts on e.id_patient = ts.id_patient
- left join (
- select id as id_patient from view_patient_positive
- union
- select id_patient from tracking_regime where category = 'regime_infant_treatment' and start_date between @start_date_curr and @end_date_curr
- )pos on pos.id_patient = e.id_patient
- group by patient.id
- )b on a.id=b.id_patient
- where id_patient is not null
- ) alias
- ### TO DETERMINE IF A PATIENT IS ON ARV
- left join (
- SELECT
- id_patient
- FROM
- tracking_regime
- WHERE
- category = 'regime_infant_treatment'
- AND (id_arv > 0 OR start_date != '0000-00-00')
- UNION SELECT
- id_patient
- FROM
- tracking_followup
- WHERE
- on_arv = 1
- ) ab on ab.id_patient = alias.id_patient
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement