USE caris_db;
SET #start_date for Q1
@start_date = "2019-04-01";
SET #end_date for Q1
@end_date = "2019-06-30";
SET #start_date for Q2
@start_date_curr = "2019-07-01";
SET #end_date for Q2
@end_date_curr = "2019-09-30";
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
)
union select id_patient from questionnaire_child where date between @start_date
and @end_date
) 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 questionnaire_child qc on qc.id_patient = e.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,
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 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',
'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_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)< 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_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)< 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_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
Comments
0 B
|👍
/👎
0 B
|👍
/👎