[导入]花了一天写的存储过程

[导入]花了一天写的存储过程

--*********************************************************--
--新建日期 2008-07-23 新建者 CK
--修改时间
--修改者
--*********************************************************--
-------------------------------------------------------------
--参数
declare @QBusinessDate1 datetime--业务时间
set @QBusinessDate1='1900-01-01'
declare @QBusinessDate2 datetime--业务时间
set @QBusinessDate2='2008-07-22'
--------------------------------------------------------
declare @IsFood int--食品类、非食品类       
set @IsFood=1
--------------------------------------------------------
declare @Category nvarchar(50)--类别名        --内部变量        
declare @Num int--检验批次
declare @AvailableNum int--合格批次
declare @AvailableRate float--合格率
declare @ClientNum int--检验企业个数
declare @CANum int--不合格企业个数
declare @CARate float--不合格企业率
--------------------------------------------------------
declare @i int        --循环计数器
declare @j int
---------------------------------------------------------------------------------------------------------------
--新建临时表
create table #tmp_table(Catagory nvarchar(50),Batch int,Batch_Pass int,PassRate float,Corporation int,
 Corporation_False int,FalseRate float)
---------------------------------------------------------------------------------------------------------------
--获取合计信息
 select @Num=count(*) from Spe_HYStatic_TotalBisInfo where IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取检验批次
 select @AvailableNum=count(*) from Spe_HYStatic_TotalBisInfo where TestConclusion=1 and IsFood=1
  and (Date between @QBusinessDate1 and @QBusinessDate2)--获取合格批次
 --------------------------------------
 if(@Num=0)--除数为0，合格率做相应处理
 begin
  set @AvailableRate=0
 end
 else
 begin
  set @AvailableRate=(@AvailableNum*100)/@Num--获取合格率
 end
 ---------------------------------------
 select @ClientNum=count(distinct BeCheckClient) from Spe_HYStatic_TotalBisInfo where IsFood=1
  and (Date between @QBusinessDate1 and @QBusinessDate2)--获取企业数
 select @CANum =count(distinct BeCheckClient) from Spe_HYStatic_TotalBisInfo where TestConclusion=0
  and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取不合格企业数
 ---------------------------------------
 if(@ClientNum=0)--除数为0，企业不合格率做相应处理
 begin
  set @CARate=0
 end
 else
 begin
 set @CARate=@CANum*100/@ClientNum--获取企业不合格率
 end
 ----------------------------------------
 insert into #tmp_table--把记录插入临时表中
 values(
 '00合计' ,
 @Num ,
 @AvailableNum ,
 @AvailableRate,
 @ClientNum ,
 @CANum,
 @CARate)
---------------------------------------------------------------------------------------------------------------
--新建临时表，分别存放一级类别
create table #tmp_LevelOne(NODEID int,NODENAME nvarchar(50),PARENTID int)
insert into #tmp_LevelOne
  select NODEID,NODENAME,PARENTID from Spe_FoodCategory where PARENTID=0
---------------------------------------------------------------------------------------------------------------
declare @OneMAX int--最大ID                  
declare @OneMIN int--循环最小ID
---------------------------------------------------------
--初始化
select @OneMAX=max(NODEID) from #tmp_LevelOne
select @OneMIN=min(NODEID) from #tmp_LevelOne
set @i=1
---------------------------------------------------------------------------------------------------------------
--循环类别表，按类别ID统计相关信息
while(@OneMIN<=@OneMAX)
begin
 if exists (select 1 from #tmp_LevelOne where NODEID=@OneMIN)
 begin
  select @Category=NODENAME from #tmp_LevelOne where NODEID=@OneMIN--获取类别名
  -----------------------------------------------
  if(len(convert(nvarchar,@i))=1)
  begin
   set @Category='0'+convert(nvarchar,@i)+@Category
  end
  else
  begin
   set @Category=convert(nvarchar,@i)+@Category
  end
  -----------------------------------------------
  select @Num=count(*) from Spe_HYStatic_TotalBisInfo where CatID=@OneMIN and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取检验批次
  select @AvailableNum=count(*) from Spe_HYStatic_TotalBisInfo where CatID=@OneMIN and TestConclusion=1
   and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取合格批次
  --------------------------------------
  if(@Num=0)--除数为0，合格率做相应处理
  begin
   set @AvailableRate=0
  end
  else
  begin
   set @AvailableRate=(@AvailableNum*100)/@Num--获取合格率
  end
  ---------------------------------------
  select @ClientNum=count(distinct BeCheckClient) from Spe_HYStatic_TotalBisInfo where CatID=@OneMIN
   and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取企业数
  select @CANum =count(distinct BeCheckClient) from Spe_HYStatic_TotalBisInfo where CatID=@OneMIN
   and TestConclusion=0 and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取不合格企业数
  ---------------------------------------
  if(@ClientNum=0)--除数为0，企业不合格率做相应处理
  begin
   set @CARate=0
  end
  else
  begin
  set @CARate=@CANum*100/@ClientNum--获取企业不合格率
  end
  ----------------------------------------
  insert into #tmp_table--把记录插入临时表中
  values(
  @Category ,
  @Num ,
  @AvailableNum ,
  @AvailableRate,
  @ClientNum ,
  @CANum,
  @CARate)
  ----------------------------------------
  --新建临时表，分别存放二级类别
  create table #tmp_LevelTwo(NODEID int,NODENAME nvarchar(50),PARENTID int)
  insert into #tmp_LevelTwo
    select NODEID,NODENAME,PARENTID from Spe_FoodCategory where PARENTID=@OneMIN
  ----------------------------------------
  declare @TwoMAX int--最大ID 
  declare @TwoMIN int--循环最小ID
  ----------------------------------------
  --初始化
  select @TwoMAX=max(NODEID) from #tmp_LevelTwo
  select @TwoMIN=min(NODEID) from #tmp_LevelTwo
  set @j=1
  ----------------------------------------
  while(@TwoMIN<=@TwoMAX)
  begin
   if exists (select 1 from #tmp_LevelTwo where NODEID=@TwoMIN)
   begin
    select @Category=NODENAME from #tmp_LevelTwo where NODEID=@TwoMIN--获取类别名
    ----------------------------------------------------------
    set @Category=
     (case when len(convert(nvarchar,@i))=1 then '0'+ convert(nvarchar,@i)
      else convert(nvarchar,@i) end)
     +(case when len(convert(nvarchar,@j))=1 then '0'+ convert(nvarchar,@j)
      else convert(nvarchar,@j) end)
     +@Category
    ----------------------------------------------------------
    select @Num=count(*) from Spe_HYStatic_TotalBisInfo where CatID=@TwoMIN and IsFood=1
     and (Date between @QBusinessDate1 and @QBusinessDate2)--获取检验批次
    select @AvailableNum=count(*) from Spe_HYStatic_TotalBisInfo where CatID=@TwoMIN and TestConclusion=1
     and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取合格批次
    --------------------------------------
    if(@Num=0)--除数为0，合格率做相应处理
    begin
     set @AvailableRate=0
    end
    else
    begin
     set @AvailableRate=(@AvailableNum*100)/@Num--获取合格率
    end
    ---------------------------------------
    select @ClientNum=count(distinct BeCheckClient) from Spe_HYStatic_TotalBisInfo where CatID=@TwoMIN
     and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取企业数
    select @CANum =count(distinct BeCheckClient) from Spe_HYStatic_TotalBisInfo where CatID=@TwoMIN
     and TestConclusion=0 and IsFood=1 and (Date between @QBusinessDate1 and @QBusinessDate2)--获取不合格企业数
    ---------------------------------------
    if(@ClientNum=0)--除数为0，企业不合格率做相应处理
    begin
     set @CARate=0
    end
    else
    begin
    set @CARate=@CANum*100/@ClientNum--获取企业不合格率
    end
    ----------------------------------------
    insert into #tmp_table--把记录插入临时表中
    values(
    @Category ,
    @Num ,
    @AvailableNum ,
    @AvailableRate,
    @ClientNum ,
    @CANum,
    @CARate)
    -------------------------------
    set @j=@j+1
   end
   set @TwoMIN=@TwoMIN+1--循环变量增1
  end
  ----------------------------------------
  --销毁临时表
  drop table #tmp_LevelTwo
  set @i=@i+1
 end
 set @OneMIN=@OneMIN+1--循环变量增1
end
--销毁临时表
drop table #tmp_LevelOne
---------------------------------------------------------------------------------------------------------------
--输出临时表
select Catagory as ' ',Corporation as '监督检验企业数(个)',Corporation_False as '查出不合格产品企业数(个)',
 FalseRate as '不合格产品企业所占比例%',Batch as '检验批次', Batch_Pass as '合格批次',
 PassRate as '批次合格率(%)' from #tmp_table
---------------------------------------------------------------------------------------------------------------
--销毁临时表
drop table #tmp_table