参数表函数

create function Call_Ins(@Reg nvarchar(20),@Dist nvarchar(20),@Terr nvarchar(20),@Pro nvarchar(20),@start  nvarchar(20),@end   nvarchar(20))  returns  table  WITH ENCRYPTION  as  return  (     select  case when @Reg = '' then Reg_Name  when @Dist = '' then Dist_Name  when @Terr = '' then sfe_rdt.Terr_Code  else sfe_rdt.Terr_Code  end  name  ,count(distinct case when YYLB ='红色医院' then  sfe_rdt.Ins_Code else null end ) 红色医院,count(distinct case when YYLB ='绿色医院' then  sfe_rdt.Ins_Code else null end ) 绿色医院  ,count(distinct case when YYLB ='黄色医院' then  sfe_rdt.Ins_Code else null end ) 黄色医院  ,count(distinct case when YYLB ='新开发医院' then  sfe_rdt.Ins_Code else null end ) 新开发医院  ,count(distinct case when YYLB ='红色医院' then  SFE_CALL_Ins.Ins_Code else null end ) 红色医院拜访,count(distinct case when YYLB ='绿色医院' then  SFE_CALL_Ins.Ins_Code else null end ) 绿色医院拜访 ,count(distinct case when YYLB ='黄色医院' then  SFE_CALL_Ins.Ins_Code else null end ) 黄色医院拜访  ,count(distinct case when YYLB ='新开发医院' then  SFE_CALL_Ins.Ins_Code else null end ) 新开发医院拜访  ,isnull(count(distinct SFE_CALL_Ins.Ins_Code )*1./nullif(count(distinct sfe_rdt.Ins_Code),0),0) 拜访覆盖率  from sfe_rdt left join SFE_CALL_Ins on  case when @Dist<>'' then sfe_rdt.Terr_Code  else  SFE_CALL_Ins.Terr_Code end = SFE_CALL_Ins.Terr_Code collate Chinese_PRC_CI_AS  and sfe_rdt.Ins_Code = SFE_CALL_Ins.Ins_Code  collate Chinese_PRC_CI_AS  and sfe_rdt.Prod_Code=SFE_CALL_Ins.Prod_Code  collate Chinese_PRC_CI_AS  and 期间 between @start  and @end  where sfe_rdt.Terr_Code is not null  and sfe_rdt.Dist_Name is not null  and sfe_rdt.Reg_Name is not null  and sfe_rdt.Ins_Code is not null  and (case when   @Pro <> '' then @Pro else '1' end)=(case when   @Pro <> '' then sfe_rdt.Prod_Code else '1' end)  and (case when   @Reg <> '' then @Reg else '1' end)=(case when   @Reg <> '' then Reg_Name else '1' end)  and (case when   @Dist <> '' then @Dist else '1' end)=(case when   @Dist <> '' then Dist_Name else '1' end)  and (case when   @Terr <> '' then @Terr else '1' end)=(case when   @Terr <> '' then sfe_rdt.Terr_Code  else '1' end)  group  by case when @Reg = '' then Reg_Name  when @Dist = '' then Dist_Name  when @Terr = '' then sfe_rdt.Terr_Code  else sfe_rdt.Terr_Code  end  );  p6 alter function sales_p6 (@Reg nvarchar(20),@Dist nvarchar(20),@Terr nvarchar(20),@Pro nvarchar(20),@start  nvarchar(20),@end   nvarchar(20))  returns  table  WITH ENCRYPTION  as  return  (  with sales as (  select   case when @Reg = '' then sfe_rdt.Reg_Name  when @Dist = '' then sfe_rdt.Dist_Name  when @Terr = '' then sfe_rdt.Terr_Code  else sfe_rdt.Terr_Code  end  name  ,sfe_rdt.Prod_Code  ,sum( case when sfe_rdt.YYLB='红色医院' then  sfe_ins_sales.amt*isnull(Factor,1) else 0 end)  红色医院  ,sum( case when sfe_rdt.YYLB='绿色医院' then  sfe_ins_sales.amt*isnull(Factor,1) else 0 end)  绿色医院  ,sum( case when sfe_rdt.YYLB='黄色医院' then  sfe_ins_sales.amt*isnull(Factor,1) else 0 end)  黄色医院  ,sum( case when sfe_rdt.YYLB='新开发医院' then  sfe_ins_sales.amt*isnull(Factor,1) else 0 end)  新开发医院  ,sum( sfe_ins_sales.amt*isnull(Factor,1)) amt  from sfe_rdt left join sfe_ins_sales on  sfe_ins_sales.Ins_Code = sfe_rdt.Ins_Code  collate Chinese_PRC_CI_AI                                      and sfe_ins_sales.Prod_Code = sfe_rdt.Prod_code  collate Chinese_PRC_CI_AI                                      and [date] between @start and @end  collate Chinese_PRC_CI_AI               left join sfe_factor on  sfe_factor.Ins_Code = sfe_rdt.Ins_Code  collate Chinese_PRC_CI_AI                                    and sfe_factor.Prod_Code = sfe_rdt.Prod_code  collate Chinese_PRC_CI_AI                                    and sfe_factor.Terr_Code = sfe_rdt.Terr_Code   collate Chinese_PRC_CI_AI                                    and sfe_factor.fdate = sfe_ins_sales.Fdate   where  sfe_rdt.Terr_Code  not like '%00'   and (case when   @Reg <> '' then @Reg else '1' end)=(case when   @Reg <> '' then sfe_rdt.Reg_Name else '1' end)  and (case when   @Dist <> '' then @Dist else '1' end)=(case when   @Dist <> '' then sfe_rdt.Dist_Name else '1' end)  and (case when   @Terr <> '' then @Terr else '1' end)=(case when   @Terr <> '' then sfe_rdt.Terr_Code  else '1' end)  group by case when @Reg = '' then sfe_rdt.Reg_Name  when @Dist = '' then sfe_rdt.Dist_Name  when @Terr = '' then sfe_rdt.Terr_Code  else sfe_rdt.Terr_Code  end,sfe_rdt.Prod_Code  with cube    )  select name,红色医院,绿色医院,黄色医院,新开发医院 ,amt  from sales  where name is not null  and @Pro=isnull(Prod_Code,'')  );  create function P9 (@Reg nvarchar(20),@Dist nvarchar(20),@Terr nvarchar(20),@Pro nvarchar(20),@start  nvarchar(20),@end   nvarchar(20))    returns  table    WITH ENCRYPTION    as    return    (          select yss.目标科室,yss.医生数*1./sum(医生数) over() 医生数占比,c.C数据*1./sum(C数据)　over()　C数据占比,bf.拜访*1./sum(拜访)　over()　拜访占比from (select 目标科室,(case when   @Pro <> '' then 产品 else '' end)  产品,isnull(count(医生编码),0) 医生数 from new_kudetail where   (case when   @Pro <> '' then @Pro else '1' end)=(case when   @Pro <> '' then 产品 else '1' end)  and (case when   @Reg <> '' then @Reg else '1' end)=(case when   @Reg <> '' then 大区 else '1' end)    and (case when   @Dist <> '' then @Dist else '1' end)=(case when   @Dist <> '' then 地区 else '1' end)    and (case when   @Terr <> '' then @Terr else '1' end)=(case when   @Terr <> '' then new_kudetail.小区编码  else '1' end)   and 产品 in ('天普洛安','凯力康')and 目标科室 is not null group by 目标科室,(case when   @Pro <> '' then 产品 else '' end)   )  yss left join  (select 目标科室,(case when   @Pro <> '' then 产品 else '' end)  产品,isnull(sum(sl),0) C数据from new_kudetail left join SFE_Ku_C on new_kudetail.Id = SFE_Ku_C.New_kudetailId and SFE_Ku_C.ny  between  @start and  @endwhere (case when   @Pro <> '' then @Pro else '1' end)=(case when   @Pro <> '' then 产品 else '1' end)  and (case when   @Reg <> '' then @Reg else '1' end)=(case when   @Reg <> '' then 大区 else '1' end)    and (case when   @Dist <> '' then @Dist else '1' end)=(case when   @Dist <> '' then 地区 else '1' end)    and (case when   @Terr <> '' then @Terr else '1' end)=(case when   @Terr <> '' then new_kudetail.小区编码  else '1' end)   and 产品 in ('天普洛安','凯力康')and 目标科室 is not null group by 目标科室,(case when   @Pro <> '' then 产品 else '' end)   ) C on yss.目标科室 = c.目标科室 left join ( select 目标科室,(case when   @Pro <> '' then 产品 else '' end)  产品,isnull(sum(cs),0) 拜访 from new_kudetail left join SFE_CALL_Doc  on new_kudetail.医生编码 = SFE_CALL_Doc.Doc_Code  collate Chinese_PRC_CI_AS and new_kudetail.小区编码= SFE_CALL_Doc.Terr_Code  collate Chinese_PRC_CI_AS  and new_kudetail.产品= SFE_CALL_Doc.Prod_Code  collate Chinese_PRC_CI_AS  and new_kudetail.医院编码=SFE_CALL_Doc.Ins_Code  collate Chinese_PRC_CI_AS and SFE_CALL_Doc.期间 between  @start and  @end where   (case when   @Pro <> '' then @Pro else '1' end)=(case when   @Pro <> '' then 产品 else '1' end)  and (case when   @Reg <> '' then @Reg else '1' end)=(case when   @Reg <> '' then 大区 else '1' end)    and (case when   @Dist <> '' then @Dist else '1' end)=(case when   @Dist <> '' then 地区 else '1' end)    and (case when   @Terr <> '' then @Terr else '1' end)=(case when   @Terr <> '' then new_kudetail.小区编码  else '1' end)   and 产品 in ('天普洛安','凯力康')and 目标科室 is not null group by 目标科室,(case when   @Pro <> '' then 产品 else '' end)   ) bf on yss.目标科室 = bf.目标科室 -------------------------------------------------------------------------);