- โค้ด: เลือกทั้งหมด
SELECT q1.visit as type
,max(q1.opd_in_cup) as opd_in_cup
,max(q1.opd_out_cup) as opd_out_cup
,max(q1.ipd_in_cup) as ipd_in_cup
,max(q1.ipd_out_cup) as ipd_out_cup
,sum(q1.opd_in_cup + q1.opd_out_cup + q1.ipd_in_cup + q1.ipd_out_cup) as รวม
FROM
(select case when t_visit.visit_first_visit = '1' then 'ผู้ป่วยใหม่'
when t_visit.visit_first_visit = '0' then 'ผู้ป่วยเก่า'
end as visit
,count(distinct case when t_patient.patient_amphur = '601100' and t_visit.f_visit_type_id = '0' then t_visit.t_patient_id else null end) as opd_in_cup
,count(distinct case when t_patient.patient_amphur <> '601100' and t_visit.f_visit_type_id = '0' then t_visit.t_patient_id else null end) as opd_out_cup
,count(distinct case when t_patient.patient_amphur = '601100' and t_visit.f_visit_type_id = '1' then t_visit.t_patient_id else null end) as ipd_in_cup
,count(distinct case when t_patient.patient_amphur <> '601100' and t_visit.f_visit_type_id = '1' then t_visit.t_patient_id else null end) as ipd_out_cup
,count(distinct case when t_visit.f_visit_type_id = '0' then t_visit.t_patient_id else null end) as opd
,count(distinct case when t_visit.f_visit_type_id = '1' then t_visit.t_patient_id else null end) as ipd
,case when t_visit.visit_first_visit = '1' then count(distinct t_visit.t_patient_id)
when t_visit.visit_first_visit = '0' then count(distinct t_visit.t_patient_id) else NULL end as รวม
from
t_patient
inner join t_visit on t_patient.t_patient_id = t_visit.t_patient_id
where
t_visit.f_visit_status_id <> '4'
and substring(t_visit.visit_financial_discharge_time,1,10) Between '2554-03-01' and '2554-05-01'--substring(?,1,10) AND substring(?,1,10)
group by
t_visit.visit_first_visit
,t_visit.f_visit_type_id
) as q1
GROUP BY q1.visit
อย่าลืมเปลี่ยนรหัสอำเภอเป็นของตัวเองนะคะ (ตรงที่ ไฮไลท์สีแดงค่ะ)
,count(distinct case when t_patient.patient_amphur = '601100' and t_visit.f_visit_type_id = '0' then t_visit.t_patient_id else null end) as opd_in_cup
,count(distinct case when t_patient.patient_amphur <> '601100' and t_visit.f_visit_type_id = '0' then t_visit.t_patient_id else null end) as opd_out_cup
,count(distinct case when t_patient.patient_amphur = '601100' and t_visit.f_visit_type_id = '1' then t_visit.t_patient_id else null end) as ipd_in_cup
,count(distinct case when t_patient.patient_amphur <> '601100' and t_visit.f_visit_type_id = '1' then t_visit.t_patient_id else null end) as ipd_out_cup
