36 lines
1.9 KiB
MySQL
36 lines
1.9 KiB
MySQL
|
(select vt.name as 'Visit Type',
|
||
|
|
rag.name AS 'Patient Age Group',
|
||
|
|
sum(IF(p.gender = 'F', 1, 0)) AS 'Female',
|
||
|
|
sum(IF(p.gender = 'M', 1, 0)) AS 'Male',
|
||
|
|
sum(IF(p.gender = 'O', 1, 0)) AS 'Other',
|
||
|
|
sum(IF(p.gender in ('F', 'M', 'O'), 1, 0)) AS 'Total'
|
||
|
|
from visit as v
|
||
|
|
inner join visit_type as vt on vt.visit_type_id = v.visit_type_id
|
||
|
|
inner join encounter ec on ec.visit_id = v.visit_id
|
||
|
|
inner join obs o on o.encounter_id = ec.encounter_id
|
||
|
|
inner join concept_view c on o.concept_id = c.concept_id and
|
||
|
|
c.concept_full_name = 'Registration Patient Category'
|
||
|
|
and o.voided = 0
|
||
|
|
and cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#'
|
||
|
|
inner join person p on p.person_id = o.person_id
|
||
|
|
inner join reporting_age_group rag ON rag.min_years <= TIMESTAMPDIFF(YEAR, p.birthdate, o.date_created) and
|
||
|
|
rag.max_years >= TIMESTAMPDIFF(YEAR, p.birthdate, o.date_created)
|
||
|
|
and rag.report_group_name = 'Registration'
|
||
|
|
group by vt.name, rag.sort_order
|
||
|
|
ORDER BY rag.sort_order)
|
||
|
|
union all
|
||
|
|
(select vt.name as 'Visit Type',
|
||
|
|
'Total Patient',
|
||
|
|
'',
|
||
|
|
'',
|
||
|
|
'',
|
||
|
|
count(v.patient_id)
|
||
|
|
from visit as v
|
||
|
|
inner join visit_type as vt on vt.visit_type_id = v.visit_type_id
|
||
|
|
inner join encounter ec on ec.visit_id = v.visit_id
|
||
|
|
inner join obs o on o.encounter_id = ec.encounter_id
|
||
|
|
inner join concept_view c
|
||
|
|
on o.concept_id = c.concept_id and c.concept_full_name = 'Registration Patient Category'
|
||
|
|
and cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#' and o.voided = 0
|
||
|
|
group by vt.name)
|