开发实例终稿

三张表如下：

create table license_log (
   table_name           varchar(50)                    not null,
   startid              numeric(20)                    not null,
   endid                numeric(20)                    not null,
   processtime          datetime                       default getdate() not null
)
create table maxstat (
   sdate                char(8)                        null,
   timevalue            char(6)                        null,
   num                  numeric(20)                    null
)
create table maxstat_gather (
   sdate                char(8)                        null,
   timevalue            char(6)                        null,
   num                  numeric(20)                    null,
   id                   numeric(20)                    identity
)

共有俩个存储过程，一个用于收集一天中各个时刻的数据量，另外一个收集一天当中某时刻的最大值

create procedure p_maxstat_gather_proc  as
/*
创建一个存储过程来统计某天某时刻的盲秒数
*/
begin
  declare @processdays int,@translimit int
  declare @tablestr char(4)
  declare @day  int
  declare @sqlstr varchar(2000),@maxid numeric(20)
 
    select @processdays =  intvalue  from running_parameter where paramname = 'transdays' --选择要传入几天的数据
    select @translimit =  intvalue  from running_parameter where paramname = 'translimit' --选择最多可以传多少条数据
   
    select @day = @processdays
  while (@day > 0)
  begin
    declare  @startid numeric(20)
 declare  @minid numeric(20),@endid  numeric(20)
    select @tablestr=substring(convert(char(8),dateadd(day,-@day,getdate()),112),5,4)
    select @startid = isnull(max(endid),0)+1  from license_log   where table_name='websubmitlog_t'+@tablestr 
   
    select @sqlstr = 'select @minid = isnull(min(billindex),1),  @endid = isnull(max(billindex),0) from websubmitlog_t'+@tablestr
 execute(@sqlstr)
    if(@minid > @startid)
    select @startid = @minid
   
    if (@endid >= @startid)
    begin
     if (@endid > @startid + @translimit)
     begin
         select @endid = @startid + @translimit
     end
        -----processlog
        if not exists (select 1 from license_log where table_name = 'websubmitlog_t'+@tablestr)
        begin
           insert into license_log(table_name,startid,endid,processtime)
        values('websubmitlog_t'+@tablestr,1,0,getdate())
        end
        select @maxid = isnull(max(id),0) from maxstat_gather
       ----gather  websubmitlog_t data
       select @sqlstr='  insert into maxstat_gather(sdate,timevalue,num) select substring(convert(varchar(25),msgid),1,8),'
                      +'  substring(convert(varchar,msgid),9,6),count(1) from websubmitlog_t'+@tablestr
          +' where billindex>= '+ convert(varchar(20),@startid)
       +' and billindex<= ' +convert(varchar(20), @endid)
                      +' group by substring(convert(varchar,msgid),9,6),substring(convert(varchar(25),msgid),1,8)'
  exec(@sqlstr) 
        if @@error <> 0
        begin
           update license_log set startid = @startid,endid = @startid-1 where  table_name = 'websubmitlog_t'+@tablestr
           delete maxstat_gather where id > @maxid
        end
        else
      begin
            update license_log set  startid = @startid,endid = @endid  where  table_name = 'websubmitlog_t'+@tablestr
         end
     end
   
      
      ----------mmscsunmit
     
       select @startid = isnull(max(endid),0)+1  from license_log   where table_name='mmscdeliverlog_t'+@tablestr 
   
    select @sqlstr = 'select @minid = isnull(max(billindex),1), @endid = isnull(max(billindex),0) from mmscdeliverlog_t'+@tablestr
 execute(@sqlstr)
    if(@minid > @startid)
    select @startid = @minid
   
   
    if (@endid >= @startid)
    begin
     if (@endid > @startid + @translimit)
     begin
         select @endid = @startid + @translimit
     end
        -----processlog
        if not exists (select 1 from license_log where table_name = 'mmscdeliverlog_t'+@tablestr)
        begin
           insert into license_log(table_name,startid,endid,processtime)
        values('mmscdeliverlog_t'+@tablestr,1,0,getdate())
        end
        select @maxid = isnull(max(id),0) from maxstat_gather
       ----gather  websubmitlog_t data
       select @sqlstr='  insert into maxstat_gather(sdate,timevalue,num) select substring(convert(varchar(25),msgid),1,8),'
                      +'  substring(convert(varchar,msgid),9,6) ,count(*) from mmscdeliverlog_t'+@tablestr
          +' where billindex>= '+ convert(varchar(20),@startid)
       +' and billindex<= ' +convert(varchar(20), @endid)
                      +' group by substring(convert(varchar,msgid),9,6),substring(convert(varchar(25),msgid),1,8)'
  exec(@sqlstr) 
        if @@error <> 0
        begin
           update license_log set startid = @startid,endid = @startid-1 where  table_name = 'mmscdeliverlog_t'+@tablestr
           delete maxstat_gather where id > @maxid
        end
        else
      begin
            update license_log set startid = @startid,endid = @endid  where  table_name = 'mmscdeliverlog_t'+@tablestr
         end
     end
      
      
    select @day=@day-1      
    

  end
 
  -------------------------delehis
  if(datepart(hour,getdate())<4)
  begin
  delete maxstat_gather where sdate< convert(char(8), dateadd(day, -@processdays,getdate()),112)
  end
 
 
end
go
-------------------------------------------------------------------------------------------------------------------------------------------

 

create procedure p_maxstat_proc  as
begin
   declare @startid  numeric(20),@endid  numeric(20)
   declare @daynum int,@sdate varchar(8)
  
   if not exists(select 1 from license_log where table_name ='maxstat_gather')
   begin
      insert into license_log(table_name,startid,endid,processtime)
      values('maxstat_gather',1,0,getdate())
   end
  
   select @startid = isnull(max(endid),0)+1 from license_log where table_name ='maxstat_gather'
   select @endid = isnull(max(id),0) from maxstat_gather
  
   if object_id ('tempdb..#temp_time') is not null
   drop table  #temp_time
   select distinct sdate into #temp_time from maxstat_gather where id >=@startid
  
   select @daynum = count(1) from #temp_time
  
   while (@daynum >0 )
   begin
      select top 1  @sdate = sdate from #temp_time
     
      delete maxstat where sdate = @sdate
     
      insert into maxstat (sdate ,timevalue,num)
      select top 1 sdate ,timevalue,sum(num) num  from maxstat_gather
      where sdate = @sdate
      group by sdate ,timevalue
      order by num desc
     
      delete #temp_time where sdate = @sdate
      
      select @daynum = count(1) from #temp_time
   end
 
  update license_log set startid = @startid,endid = @endid,processtime = getdate() where table_name ='maxstat_gather'
 
 
  -----------delete his
  if (day(getdate()) = 2)
  begin
     declare @keepmonth int
     select @keepmonth = 25
     delete maxstat where sdate <  convert(char(8),dateadd(month,-@keepmonth,getdate()),112)
  end
end
go