SQL3

use ruanxiaowen571_ind;
drop table summary3month;
CREATE table summary3month AS
select insurance_no, sum( case when visit_type='1' and out_hosp_date is not null and in_hosp_date is not null then  (datediff(out_hosp_date,in_hosp_date)+1) else 0 end) hosp_days,
COUNT(DISTINCT(case when visit_type='1' then visit_no else null end)) hosp_freq,
COUNT(DISTINCT(case when visit_type='2' then visit_no else null end)) visit_freq,
COUNT(DISTINCT(case when diagnose_code is not null then diagnose_code else null end)) diagnose_num,COUNT(distinct medical_org_code) as org_num,count(DISTINCT bill_no) as bill_num,count(DISTINCT bill_detail_no) as detail_num,
--看医生数,看病月数
count (distinct doctor_code) as doctor_num,
count (distinct month(in_hosp_date)) as month_num,
--住院药品数，诊疗项目数，服务设施数,总金额，总赔付
count (distinct (case when (visit_type="1" and project_type="1") then project_name else null end)) as hosp_medicine_num,
count (distinct (case when (visit_type="1" and project_type="2") then project_name else null end)) as hosp_check_num,
count (distinct (case when (visit_type="1" and project_type="3") then project_name else null end)) as hosp_service_num,
sum(case when visit_type='1' then mx_sum_amount else null end) as hosp_sum_amount,
sum(case when visit_type='1' then mx_apply_pay_amount else null end) as hosp_apply_amount,


--门诊药品数，诊疗项目数，服务设施数,总金额，总赔付
count (distinct (case when (visit_type="2" and project_type="1") then project_name else null end)) as visit_medicine_num,
count (distinct (case when (visit_type="2" and project_type="1") then bill_detail_no else null end)) as visit_medicine_freq,
count (distinct (case when (visit_type="2" and project_type="2") then project_name else null end)) as visit_check_num,
count (distinct (case when (visit_type="2" and project_type="2") then bill_detail_no else null end)) as visit_check_freq,
count (distinct (case when (visit_type="2" and project_type="3") then project_name else null end)) as visit_service_num,
count (distinct (case when (visit_type="2" and project_type="3") then bill_detail_no else null end)) as visit_service_freq,
sum(case when visit_type='2' then mx_sum_amount else null end) as visit_sum_amount,
sum(case when visit_type='2' then mx_apply_pay_amount else null end) as visit_apply_amount


from ruanxiaowen571_ind.rxw_xmsb_djmxypb 


where (visit_type = '1' and 
       year(in_hosp_date) ="2014" ) or 
       (visit_type = '2' and year(in_hosp_date) ="2014" ) 
group BY insurance_no;