月报：
Code
--月报
CREATE PROCEDURE Proc_StatMonth
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --上个月信息总量
declare @upbadinfocount int --上个月负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
declare @y varchar(10) --年
declare @m varchar(10) --月
declare @d varchar(30) --当月开始日期
set @y= cast(DATEPART(year,@Date) as varchar(30))
set @m=cast(datepart(month,@Date) as varchar(30))
set @d=@y + '-' + @m + '-01'
set @infocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @d and fld_CpyID=@CopyID ) --总量
if @infocount is null
set @infocount=0
set @badinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @d and fld_CpyID=@CopyID ) --负面信息总量
if @badinfocount is null
set @badinfocount=0
--上个月信息总量
declare @upd varchar(30) --上个月开始日期
if @m='1'
set @upd = cast((cast(@y as int)-1) as varchar(10)) + '-12-01'
else
set @upd = @y + '-'+ cast((cast(@m as int)-1) as varchar(10)) + '-01'
set @upinfocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @d and fld_CpyID=@CopyID ) --上个月总量
if @upinfocount is null
set @upinfocount=0
set @upbadinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @d and fld_CpyID=@CopyID ) --上个月负面信息总量
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent =0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=1 and fld_cpyid=0
set @sBody=replace(@sBody,'#total',@infocount)
set @sBody=replace(@sBody,'#percent',cast(@totalpercent as varchar) + '%' )
set @sBody=replace(@sBody,'#BadTotal',@badinfocount)
set @sBody=replace(@sBody,'#BadPercent',cast(@totalbadpercent as varchar)+'%')
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('月报',1,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
季报：
Code
--季报
CREATE PROCEDURE Proc_StatSeason
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --上个季度信息总量
declare @upbadinfocount int --上个季度负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
-- 1,2,3 / 4,5,6 / 7,8,9 / 10,11,12 (四季)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
declare @y varchar(10) --年
declare @m varchar(10) --月
declare @d varchar(30) --目前季度开始日期
declare @ed varchar(30) --目前季度截止日期
declare @upd varchar(30) --上个季度开始日期
declare @ued varchar(30) --上个季度结束日期
set @y= cast(DATEPART(year,@Date) as varchar(30))
set @m=cast(datepart(month,@Date) as varchar(30))
set @ed=@Date
if @m in ('1','2','3')
begin
set @d=@y + '-' + '01-01'
set @upd= cast((cast(@y as int)-1) as varchar(10))+'-10-01'
set @ued= @y + '-' + '01-01'
end
else if @m in ('4','5','6')
begin
set @d=@y + '-' + '04-01'
set @upd=@y + '-' + '01-01'
set @ued= @y + '-' + '04-01'
end
else if @m in ('7','8','9')
begin
set @d=@y + '-' + '07-01'
set @upd=@y + '-' + '04-01'
set @ued= @y + '-' + '07-01'
end
else if @m in ('10','11','12')
begin
set @d=@y + '-' + '10-01'
set @upd=@y + '-' + '07-01'
set @ued= @y + '-' + '10-01'
end
set @infocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @d and fld_Date<=@ed and fld_CpyID=@CopyID ) --总量
if @infocount is null
set @infocount=0
set @badinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @d and fld_Date<=@ed and fld_CpyID=@CopyID ) --负面信息总量
if @badinfocount is null
set @badinfocount=0
--上个季度信息总量
set @upinfocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @ued and fld_CpyID=@CopyID ) --上个季度总量
if @upinfocount is null
set @upinfocount=0
set @upbadinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @ued and fld_CpyID=@CopyID ) --上个季度负面信息总量
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent=0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=2 and fld_cpyid=0
set @sBody=replace(@sBody,'#total',@infocount)
set @sBody=replace(@sBody,'#percent',cast(@totalpercent as varchar)+'%' )
set @sBody=replace(@sBody,'#BadTotal',@badinfocount)
set @sBody=replace(@sBody,'#BadPercent',cast(@totalbadpercent as varchar)+'%' )
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('季报',2,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
年报:
Code
--统计年报
CREATE PROCEDURE Proc_StatYear --年报
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --去年信息总量
declare @upbadinfocount int --去年负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
declare @y varchar(30)
set @y= cast(DATEPART(year,@Date) as varchar(30)) +'-01-01'
set @infocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @y and fld_CpyID=@CopyID ) --总量
if @infocount is null
set @infocount=0
set @badinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @y and fld_CpyID=@CopyID ) --负面信息总量
if @badinfocount is null
set @badinfocount=0
--去今年信息总量
declare @upy varchar(30) --去年日期
set @upy = cast( (cast( DATEPART(year,@Date) as int ) -1) as varchar(30) ) +'-01-01'
set @upinfocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @upy and fld_Date < @y and fld_CpyID=@CopyID ) --去年总量
if @upinfocount is null
set @upinfocount=0
set @upbadinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @upy and fld_Date < @y and fld_CpyID=@CopyID ) --去年负面信息总量
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent=0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=3 and fld_cpyid=0
set @sBody=replace(@sBody,'#total', cast(@infocount as varchar) )
set @sBody=replace(@sBody,'#percent', cast(@totalpercent as varchar) +'%')
set @sBody=replace(@sBody,'#BadTotal', cast(@badinfocount as varchar) )
set @sBody=replace(@sBody,'#BadPercent', cast(@totalbadpercent as varchar)+'%')
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('年报',3,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
周报:
Code
--周报
CREATE PROCEDURE Proc_StartWeek
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --上个周信息总量
declare @upbadinfocount int --上个周负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
set @infocount=(select count(fld_total) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=datename(week,getdate()-1) and datepart(year,getdate())=datepart(year,fld_date)) --总量
if @infocount is null
set @infocount=0
set @badinfocount=(select count(fld_badtotal) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=datename(week,getdate()-1) and datepart(year,getdate())=datepart(year,fld_date))--负面信息总量
if @badinfocount is null
set @badinfocount=0
--上个月信息总量
if datename(week,getdate()-1)=1
set @upinfocount=(select count(fld_total) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate())-1)=datepart(year,fld_date)))
else
set @upinfocount=(select count(fld_total) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate()))=datepart(year,fld_date)))
if @upinfocount is null
set @upinfocount=0
--上个周负面信息总量
if datename(week,getdate()-1)=1
set @upbadinfocount=(select count(fld_badtotal) from t_resultTotal where datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate())-1)=datepart(year,fld_date)) and fld_CpyID=@CopyID )
else
set @upbadinfocount=(select count(fld_badtotal) from t_resultTotal where datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate()))=datepart(year,fld_date)) and fld_CpyID=@CopyID )
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent =0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=0 and fld_cpyid=0
set @sBody=replace(@sBody,'#total',@infocount)
set @sBody=replace(@sBody,'#percent',cast(@totalpercent as varchar) + '%' )
set @sBody=replace(@sBody,'#BadTotal',@badinfocount)
set @sBody=replace(@sBody,'#BadPercent',cast(@totalbadpercent as varchar)+'%')
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('周报',1,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
Code--月报
CREATE PROCEDURE Proc_StatMonth
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --上个月信息总量
declare @upbadinfocount int --上个月负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
declare @y varchar(10) --年
declare @m varchar(10) --月
declare @d varchar(30) --当月开始日期
set @y= cast(DATEPART(year,@Date) as varchar(30))
set @m=cast(datepart(month,@Date) as varchar(30))
set @d=@y + '-' + @m + '-01'
set @infocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @d and fld_CpyID=@CopyID ) --总量
if @infocount is null
set @infocount=0
set @badinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @d and fld_CpyID=@CopyID ) --负面信息总量
if @badinfocount is null
set @badinfocount=0
--上个月信息总量
declare @upd varchar(30) --上个月开始日期
if @m='1'
set @upd = cast((cast(@y as int)-1) as varchar(10)) + '-12-01'
else
set @upd = @y + '-'+ cast((cast(@m as int)-1) as varchar(10)) + '-01'
set @upinfocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @d and fld_CpyID=@CopyID ) --上个月总量
if @upinfocount is null
set @upinfocount=0
set @upbadinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @d and fld_CpyID=@CopyID ) --上个月负面信息总量
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent =0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=1 and fld_cpyid=0
set @sBody=replace(@sBody,'#total',@infocount)
set @sBody=replace(@sBody,'#percent',cast(@totalpercent as varchar) + '%' )
set @sBody=replace(@sBody,'#BadTotal',@badinfocount)
set @sBody=replace(@sBody,'#BadPercent',cast(@totalbadpercent as varchar)+'%')
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('月报',1,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
Code--季报
CREATE PROCEDURE Proc_StatSeason
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --上个季度信息总量
declare @upbadinfocount int --上个季度负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
-- 1,2,3 / 4,5,6 / 7,8,9 / 10,11,12 (四季)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
declare @y varchar(10) --年
declare @m varchar(10) --月
declare @d varchar(30) --目前季度开始日期
declare @ed varchar(30) --目前季度截止日期
declare @upd varchar(30) --上个季度开始日期
declare @ued varchar(30) --上个季度结束日期
set @y= cast(DATEPART(year,@Date) as varchar(30))
set @m=cast(datepart(month,@Date) as varchar(30))
set @ed=@Date
if @m in ('1','2','3')
begin
set @d=@y + '-' + '01-01'
set @upd= cast((cast(@y as int)-1) as varchar(10))+'-10-01'
set @ued= @y + '-' + '01-01'
end
else if @m in ('4','5','6')
begin
set @d=@y + '-' + '04-01'
set @upd=@y + '-' + '01-01'
set @ued= @y + '-' + '04-01'
end
else if @m in ('7','8','9')
begin
set @d=@y + '-' + '07-01'
set @upd=@y + '-' + '04-01'
set @ued= @y + '-' + '07-01'
end
else if @m in ('10','11','12')
begin
set @d=@y + '-' + '10-01'
set @upd=@y + '-' + '07-01'
set @ued= @y + '-' + '10-01'
end
set @infocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @d and fld_Date<=@ed and fld_CpyID=@CopyID ) --总量
if @infocount is null
set @infocount=0
set @badinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @d and fld_Date<=@ed and fld_CpyID=@CopyID ) --负面信息总量
if @badinfocount is null
set @badinfocount=0
--上个季度信息总量
set @upinfocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @ued and fld_CpyID=@CopyID ) --上个季度总量
if @upinfocount is null
set @upinfocount=0
set @upbadinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @upd and fld_Date < @ued and fld_CpyID=@CopyID ) --上个季度负面信息总量
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent=0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=2 and fld_cpyid=0
set @sBody=replace(@sBody,'#total',@infocount)
set @sBody=replace(@sBody,'#percent',cast(@totalpercent as varchar)+'%' )
set @sBody=replace(@sBody,'#BadTotal',@badinfocount)
set @sBody=replace(@sBody,'#BadPercent',cast(@totalbadpercent as varchar)+'%' )
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('季报',2,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
年报:
Code--统计年报
CREATE PROCEDURE Proc_StatYear --年报
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --去年信息总量
declare @upbadinfocount int --去年负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
declare @y varchar(30)
set @y= cast(DATEPART(year,@Date) as varchar(30)) +'-01-01'
set @infocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @y and fld_CpyID=@CopyID ) --总量
if @infocount is null
set @infocount=0
set @badinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @y and fld_CpyID=@CopyID ) --负面信息总量
if @badinfocount is null
set @badinfocount=0
--去今年信息总量
declare @upy varchar(30) --去年日期
set @upy = cast( (cast( DATEPART(year,@Date) as int ) -1) as varchar(30) ) +'-01-01'
set @upinfocount = (select sum(fld_ToTal) from T_ResultTotal where fld_Date >= @upy and fld_Date < @y and fld_CpyID=@CopyID ) --去年总量
if @upinfocount is null
set @upinfocount=0
set @upbadinfocount = (select sum(fld_BadTotal) from T_ResultTotal where fld_Date >= @upy and fld_Date < @y and fld_CpyID=@CopyID ) --去年负面信息总量
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent=0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=3 and fld_cpyid=0
set @sBody=replace(@sBody,'#total', cast(@infocount as varchar) )
set @sBody=replace(@sBody,'#percent', cast(@totalpercent as varchar) +'%')
set @sBody=replace(@sBody,'#BadTotal', cast(@badinfocount as varchar) )
set @sBody=replace(@sBody,'#BadPercent', cast(@totalbadpercent as varchar)+'%')
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('年报',3,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
周报:
Code--周报
CREATE PROCEDURE Proc_StartWeek
AS
declare @infocount int --总信息量
declare @badinfocount int --负面信息总量
declare @upinfocount int --上个周信息总量
declare @upbadinfocount int --上个周负面信息总量
declare @Date datetime --日期
declare @CopyID int --公司id
declare @sBody varchar(1000) --字段内容模板
declare @totalpercent float
declare @totalbadpercent float
set @totalpercent=0
set @totalbadpercent=0
set @Date = convert(varchar,getdate(),23) --此时日期(短日期格式)
begin tran
declare cpyid cursor for select fld_ID from User_Crunode.T_CompanyInfo
open cpyid
fetch next from cpyid into @CopyID
while @@fetch_status =0
begin
set @infocount=(select count(fld_total) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=datename(week,getdate()-1) and datepart(year,getdate())=datepart(year,fld_date)) --总量
if @infocount is null
set @infocount=0
set @badinfocount=(select count(fld_badtotal) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=datename(week,getdate()-1) and datepart(year,getdate())=datepart(year,fld_date))--负面信息总量
if @badinfocount is null
set @badinfocount=0
--上个月信息总量
if datename(week,getdate()-1)=1
set @upinfocount=(select count(fld_total) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate())-1)=datepart(year,fld_date)))
else
set @upinfocount=(select count(fld_total) from t_resultTotal where fld_CpyID=@CopyID and datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate()))=datepart(year,fld_date)))
if @upinfocount is null
set @upinfocount=0
--上个周负面信息总量
if datename(week,getdate()-1)=1
set @upbadinfocount=(select count(fld_badtotal) from t_resultTotal where datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate())-1)=datepart(year,fld_date)) and fld_CpyID=@CopyID )
else
set @upbadinfocount=(select count(fld_badtotal) from t_resultTotal where datename(week,fld_date-1)=(datename(week,getdate()-1)-1) and ((datepart(year,getdate()))=datepart(year,fld_date)) and fld_CpyID=@CopyID )
if @upbadinfocount is null
set @upbadinfocount=0
if @upinfocount> 0
set @totalpercent = (@infocount- cast(@upinfocount as float) ) / @upinfocount * 100 --总信息比较值
else
set @totalpercent =0
if @upbadinfocount >0
set @totalbadpercent = (@badinfocount - cast(@upbadinfocount as float) )/@upbadinfocount * 100 --负面信息比较值
else
set @totalbadpercent=0
select @sBody=fld_body from t_reports where fld_category=0 and fld_cpyid=0
set @sBody=replace(@sBody,'#total',@infocount)
set @sBody=replace(@sBody,'#percent',cast(@totalpercent as varchar) + '%' )
set @sBody=replace(@sBody,'#BadTotal',@badinfocount)
set @sBody=replace(@sBody,'#BadPercent',cast(@totalbadpercent as varchar)+'%')
insert into t_Reports (fld_Name,fld_Category,fld_Body,fld_CpyID) values ('周报',1,@sBody,@CopyID)
fetch next from cpyid into @CopyID
end
close cpyid
deallocate cpyid
commit tran
GO
