门诊医生工作量统计.txt

在SQLSERVER2000下对数据库进行大量的工作量查询统计

declare @begin datetime,
        @end datetime
set @begin='2005-12-01'
set @end='2005-12-02'


select b.name,x.cfs,
isnull(sum(case when a.audit_code='001' then isnull(a.charge_amount*a.charge_price,0)end),0) as 西药费,
isnull(sum(case when a.audit_code='002' then isnull(a.charge_amount*a.charge_price*caoyao_fu,0)end),0) as 中草药,
isnull(sum(case when a.audit_code='003' then isnull(a.charge_amount*a.charge_price,0)end),0) as 中成药,
isnull(sum(case when a.audit_code='004' then isnull(a.charge_amount*a.charge_price,0)end),0) as 化验费,
isnull(sum(case when a.audit_code='005' then isnull(a.charge_amount*a.charge_price,0)end),0) as 放射费,
isnull(sum(case when a.audit_code='006' then isnull(a.charge_amount*a.charge_price,0)end),0) as 材料费,
isnull(sum(case when a.audit_code='007' then isnull(a.charge_amount*a.charge_price,0)end),0) as CT费,
isnull(sum(case when a.audit_code='008' then isnull(a.charge_amount*a.charge_price,0)end),0) as 脑电图,
isnull(sum(case when a.audit_code='009' then isnull(a.charge_amount*a.charge_price,0)end),0) as 心电图,
isnull(sum(case when a.audit_code='010' then isnull(a.charge_amount*a.charge_price,0)end),0) as 理疗费,
isnull(sum(case when a.audit_code='011' then isnull(a.charge_amount*a.charge_price,0)end),0) as 检查费,
isnull(sum(case when a.audit_code='012' then isnull(a.charge_amount*a.charge_price,0)end),0) as 妇检费,
isnull(sum(case when a.audit_code='013' then isnull(a.charge_amount*a.charge_price,0)end),0) as 注射费,
isnull(sum(case when a.audit_code='014' then isnull(a.charge_amount*a.charge_price,0)end),0) as 输血费,
isnull(sum(case when a.audit_code='015' then isnull(a.charge_amount*a.charge_price,0)end),0) as 治疗费,
isnull(sum(case when a.audit_code='016' then isnull(a.charge_amount*a.charge_price,0)end),0) as 手术费,
isnull(sum(case when a.audit_code='017' then isnull(a.charge_amount*a.charge_price,0)end),0) as 高压氧,
isnull(sum(case when a.audit_code='018' then isnull(a.charge_amount*a.charge_price,0)end),0) as 急诊治疗,
isnull(sum(case when a.audit_code='019' then isnull(a.charge_amount*a.charge_price,0)end),0) as TCD费,
isnull(sum(case when a.audit_code='020' then isnull(a.charge_amount*a.charge_price,0)end),0) as 乳腺诊断,
isnull(sum(case when a.audit_code='021' then isnull(a.charge_amount*a.charge_price,0)end),0) as B超,
isnull(sum(case when a.audit_code='022' then isnull(a.charge_amount*a.charge_price,0)end),0) as 内窥镜,
isnull(sum(case when a.audit_code='023' then isnull(a.charge_amount*a.charge_price,0)end),0) as 碎石,
isnull(sum(case when a.audit_code='024' then isnull(a.charge_amount*a.charge_price,0)end),0) as 救护车,
isnull(sum(case when a.audit_code='025' then isnull(a.charge_amount*a.charge_price,0)end),0) as 床位费,
isnull(sum(case when a.audit_code='026' then isnull(a.charge_amount*a.charge_price,0)end),0) as 彩超,
isnull(sum(case when a.audit_code='027' then isnull(a.charge_amount*a.charge_price,0)end),0) as 螺旋CT,
isnull(sum(case when a.audit_code='028' then isnull(a.charge_amount*a.charge_price,0)end),0) as 血液体液,
isnull(sum(case when a.audit_code='029' then isnull(a.charge_amount*a.charge_price,0)end),0) as 生化,
isnull(sum(case when a.audit_code='030' then isnull(a.charge_amount*a.charge_price,0)end),0) as 病理检查,
isnull(sum(case when a.audit_code='031' then isnull(a.charge_amount*a.charge_price,0)end),0) as 免疫细菌,
isnull(sum(case when a.audit_code='032' then isnull(a.charge_amount*a.charge_price,0)end),0) as pcr,
isnull(sum(case when isnull(a.audit_code,'033')='033' then isnull(a.charge_amount*a.charge_price,0)end),0) as 其它,
isnull(sum(case when a.audit_code='034' then isnull(a.charge_amount*a.charge_price,0)end),0) as 护理费,
isnull(sum(case when a.audit_code='035' then isnull(a.charge_amount*a.charge_price,0)end),0) as 平板,
isnull(sum(case when a.audit_code='036' then isnull(a.charge_amount*a.charge_price,0)end),0) as ICU
from view_mz_detail_charge a,a_employee_mi b,
(select sum(n.cfs) cfs,doctor_code from
(select distinct patient_id,order_no,ledger_sn,doctor_code,
(case  when ledger_sn>0 then 1 else -1 end) cfs 
from view_mz_detail_charge
where charge_status<>'1'
and view_mz_detail_charge.price_data>=@begin
and view_mz_detail_charge.price_data<cast(@end as datetime)+1
)n
group by doctor_code)x

where a.charge_status<>'1'
 and a.doctor_code=b.code
 and x.doctor_code=b.code
 and a.price_data>=@begin
 and a.price_data<=cast(@end as datetime)
group by b.name,x.cfs