20 lines
1.3 KiB
MySQL
20 lines
1.3 KiB
MySQL
|
select user.username as 'User Name',
|
||
|
|
rag.name AS 'Patient Age Group',
|
||
|
|
sum(IF(p.gender = 'M', 1, 0)) AS Male,
|
||
|
|
sum(IF(p.gender = 'F', 1, 0)) AS Female,
|
||
|
|
sum(IF(p.gender = 'O', 1, 0)) AS Other,
|
||
|
|
(sum(IF(p.gender = 'F', 1, 0)) + sum(IF(p.gender = 'M', 1, 0)) + sum(IF(p.gender = 'O', 1, 0))) AS Total
|
||
|
|
from users user
|
||
|
|
inner join provider pro on pro.person_id=user.person_id
|
||
|
|
inner join encounter_provider ep on ep.provider_id=pro.provider_id
|
||
|
|
inner join encounter ec on ec.encounter_id = ep.encounter_id
|
||
|
|
inner join visit v on ec.visit_id = v.visit_id
|
||
|
|
inner join obs o on o.encounter_id=ec.encounter_id and o.obs_id in (select obs_id from obs inner join concept_view cv on cv.concept_id=obs.concept_id and cv.concept_full_name='Registration Patient Category')
|
||
|
|
and o.voided = 0
|
||
|
|
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'
|
||
|
|
where cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#'
|
||
|
|
group by user.username, rag.name,rag.sort_order
|
||
|
|
order by user.username,rag.sort_order;
|