30 lines
2.0 KiB
SQL
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;
|