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

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

--*********************************************************--

--新建日期 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