NINS_CODE/bahmni_config/openmrs/apps/reports/sql/patientRegByAgeGroup.sql
travelershot 70dda814aa codepush
2024-12-12 22:37:39 +06:00

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;