[导入]花了一天写的存储过程
--*********************************************************--
--新建日期 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
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
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