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