54 lines
2.8 KiB
MySQL
54 lines
2.8 KiB
MySQL
|
select DATE_FORMAT(o.obs_datetime, '%d-%m-%Y') AS 'Date',
|
||
|
|
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
|
||
|
|
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 = 'outpatient'
|
||
|
|
group by cast(o.obs_datetime as date)
|
||
|
|
union all
|
||
|
|
select 'Total',
|
||
|
|
sum(IF(p.gender = 'M', 1, 0)),
|
||
|
|
sum(IF(p.gender = 'F', 1, 0)),
|
||
|
|
sum(IF(p.gender = 'O', 1, 0)),
|
||
|
|
COUNT(o.value_coded),
|
||
|
|
SUM(IF(o.value_coded in (select cv.concept_id
|
||
|
|
from concept_view cv
|
||
|
|
where concept_full_name in ('Ticket Type, Paid')), 1, 0)),
|
||
|
|
SUM(IF(o.value_coded in (select cv.concept_id
|
||
|
|
from concept_view cv
|
||
|
|
where concept_full_name in ('Ticket Type, Free')), 1, 0)),
|
||
|
|
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
|
||
|
|
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
|
||
|
|
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 = 'outpatient';
|