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

30 lines
2.0 KiB
SQL

select if(u.username <=> null, 'Total', u.username) as 'User Name',
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 Others,
COUNT(o.value_coded) AS 'Total Patient',
SUM(IF(o.value_coded in (select cv.concept_id
from concept_view cv
where concept_full_name in ('Ticket Type, Paid')), 1,
0)) AS 'Paid Patient',
SUM(IF(o.value_coded in (select cv.concept_id
from concept_view cv
where concept_full_name in ('Ticket Type, Free')), 1,
0)) AS 'Free Patient',
SUM(IF(o.value_coded in (select cv.concept_id
from concept_view cv
where concept_full_name in ('Ticket Type, Paid')), 1, 0)) *
10 AS 'Total TK.'
from obs o
inner join encounter e on o.encounter_id = e.encounter_id
inner join visit v on e.visit_id = v.visit_id
inner join visit_type vt on v.visit_type_id = vt.visit_type_id
inner join person p on p.person_id = o.person_id and p.voided = 0
inner join users u on u.user_id = o.creator and u.retired = 0
where cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#' and o.voided = 0
and o.concept_id in
(select cv.concept_id from concept_view cv where concept_full_name = 'Ticket Type')
and vt.name='emergency'
group by u.username
with rollup;