69 lines
4.0 KiB
SQL
69 lines
4.0 KiB
SQL
select DATE_FORMAT(obs_data.obs_datetime, '%d-%m-%y') AS 'Date',
|
|
CONCAT(UPPER(SUBSTRING(vt.name,1,1)),LOWER(SUBSTRING(vt.name,2))) AS 'Visit Type',
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('0-4 years')) AS '0-4 years (Male)',
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('0-4 years')) AS '0-4 years (Female)',
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('5-14 years')) AS '5-14 years (Male)',
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('5-14 years')) AS '5-14 years (Female)',
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('15-24 years')) AS '15-24 years (Male)',
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('15-24 years')) AS '15-24 years (Female)',
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('25-49 years')) AS '25-49 years (Male)',
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('25-49 years')) AS '25-49 years (Female)',
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('50+ years')) AS '50+ years (Male)',
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('50+ years')) AS '50+ years (Female)',
|
|
sum(IF(p.gender = 'M', 1, 0)) AS 'Total Male',
|
|
sum(IF(p.gender = 'F', 1, 0)) AS 'Total Female',
|
|
sum(IF(p.gender = 'O', 1, 0)) AS 'Total Others',
|
|
sum(IF(p.gender in ('F', 'M', 'O'), 1, 0)) AS 'Grand Total'
|
|
from (select o.person_id,
|
|
o.obs_datetime,
|
|
o.date_created,
|
|
o.concept_id,
|
|
o.encounter_id
|
|
from obs o
|
|
where o.voided = 0
|
|
and o.concept_id = 290
|
|
and CAST(o.obs_datetime as DATE) between '#startDate#' AND '#endDate#'
|
|
group by o.person_id, CAST(o.obs_datetime as DATE)) obs_data
|
|
inner join person p on p.person_id = obs_data.person_id
|
|
inner join reporting_age_group rag ON
|
|
rag.min_years <= TIMESTAMPDIFF(YEAR, p.birthdate, obs_data.date_created) and
|
|
rag.max_years >= TIMESTAMPDIFF(YEAR, p.birthdate, obs_data.date_created)
|
|
AND rag.report_group_name = 'Registration'
|
|
inner join visit v on obs_data.person_id = v.patient_id
|
|
inner join visit_type vt on v.visit_type_id = vt.visit_type_id
|
|
group by CAST(obs_data.obs_datetime as DATE), vt.name
|
|
union
|
|
select 'Total',
|
|
'',
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('0-4 years')),
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('0-4 years')),
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('5-14 years')),
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('5-14 years')),
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('15-24 years')),
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('15-24 years')),
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('25-49 years')),
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('25-49 years')),
|
|
sum(IF(p.gender = 'M', 1, 0) and rag.name in ('50+ years')),
|
|
sum(IF(p.gender = 'F', 1, 0) and rag.name in ('50+ years')),
|
|
sum(IF(p.gender = 'M', 1, 0)),
|
|
sum(IF(p.gender = 'F', 1, 0)),
|
|
sum(IF(p.gender = 'O', 1, 0)),
|
|
sum(IF(p.gender in ('F', 'M', 'O'), 1, 0))
|
|
from (select o.person_id,
|
|
o.obs_datetime,
|
|
o.date_created,
|
|
o.concept_id,
|
|
o.encounter_id
|
|
from obs o
|
|
where o.voided = 0
|
|
and o.concept_id = 290
|
|
and CAST(o.obs_datetime as DATE) between '#startDate#' AND '#endDate#'
|
|
group by o.person_id, CAST(o.obs_datetime as DATE)) obs_data
|
|
inner join person p on p.person_id = obs_data.person_id
|
|
inner join reporting_age_group rag ON
|
|
rag.min_years <= TIMESTAMPDIFF(YEAR, p.birthdate, obs_data.date_created) and
|
|
rag.max_years >= TIMESTAMPDIFF(YEAR, p.birthdate, obs_data.date_created)
|
|
AND rag.report_group_name = 'Registration'
|
|
inner join visit v on obs_data.person_id = v.patient_id
|
|
inner join visit_type vt on v.visit_type_id = vt.visit_type_id;
|