T-SQL 存储过程

prk/彭仁夔      08-09-02        转载注明出处。

在开发中，我们经常要用到这样的统计

 

根据用户输入的日期，把每个月的备份表的数据和正在使用的表中的综合起来统计。假如2008-05-11到2008-08-01，那么就包含5，6，7，8四张备份表还有正在使用的表，加起5张表。这是要统计的所有的数据。

 

对于统计又有要求，分情况来统计各种情况的总计，但是有的特殊情况要统计其明细。

如下图：

 T-sql日期转换不兼容。还有对运行的sql的长度也有一定的限制。

 

 

prk/彭仁夔      08-09-02        转载注明出处。

 

 

ALTER PROCEDURE [dbo].[UP_TSMReceivedDailyReport]
@StartTime varchar(100),
@EndTime    varchar(100),
@OrgAddrs  varchar(1000)='',
@MsgType_IDs  varchar(1000)=''
 AS 
     declare @where  varchar(1000)
     declare @startMonth  int 
     declare @endMonth int 
     declare @tempMonth int
     declare @generalSql varchar(1000)
     declare @tempSql varchar(1000)
     declare @tempSql1 varchar(3000)
     declare @tempTotal varchar(3000)
     declare @tempUndeal varchar(3000)
     declare @tempdealing varchar(3000)
     declare @tempdealed varchar(3000)
     declare @temp1 varchar(3000)
     declare @temp2 varchar(3000)
     declare @temp3 varchar(3000)
     declare @temp4 varchar(3000)
     declare @mm int
     declare @mm1 int
  /*
    declare @StartTime varchar(100)
    declare @EndTime    varchar(100)
    declare @OrgAddrs  varchar(1000)
    declare @MsgType_IDs  varchar(1000)
 
   set @StartTime ='2008-05-05'
   set  @EndTime  ='2008-07-07'
   set  @OrgAddrs =''
   set  @MsgType_IDs =''
*/
    set  @startMonth=Month(@StartTime)
    set  @endMonth =Month(@EndTime)
    set  @tempMonth=@startMonth
    set  @tempSql=' '
    set  @tempSql1=' '
    set  @temp1=' '
    set  @temp2=' '
    set  @temp3=' '
    set  @temp4=' '
    set  @where=' where recvTime>='''+CAST(@StartTime as varchar(20))+''' and recvTime<='''+CAST(@EndTime as varchar(20))+'''  '

    if @OrgAddrs!=''
     begin
      set @where=@where+' '+ ' and OrgAddr in '+@OrgAddrs+' '
      end
    
    if @MsgType_IDs!=''
       begin
       set @where=@where+' '+ ' and MsgType_ID in '+@MsgType_IDs+' '
       end
     print @where
     set @generalSql=' select RecvSM_ID,User_ID,MsgType_ID,OrgAddr ,DestAddr, cast(RecvTime as DateTime) RecvTime,
       SM_Content,DealSign,cast(DealTime as DateTime) DealTime,IsWait,ReMsgType_ID,SendSM_Content   
         from T_SMReceived '+@where+' and DealSign<>4  and (DealSign<>8 and iswait<>0) and (DealSign<>3 and IsWait<>0)'
       print @generalSql

create table #temp  (RecvSM_ID int, 
                      User_ID int, 
                      MsgType_ID int, 
                     OrgAddr varchar(128), 
                     DestAddr varchar(128), 
                     RecvTime datetime, 
                     SM_Content varchar(6000), 
                     DealSign tinyint, 
                     DealTime datetime,
                     IsWait bit, 
                     ReMsgType_ID int ,
                     SendSM_Content varchar(6000) )
   
    insert into #temp exec (@generalSql)
  
while @tempMonth<=@endMonth
       begin      
   set @tempSql='select RecvSM_ID,User_ID,MsgType_ID,OrgAddr ,DestAddr, cast(RecvTime as DateTime) RecvTime,
       SM_Content,DealSign,cast(DealTime as DateTime) DealTime,IsWait,ReMsgType_ID,SendSM_Content   from T_SMReceived_'+Cast(@tempMonth as varchar(2))+'  '+@where+' '
     insert into #temp  exec (@tempSql)       
       
     set @tempMonth= @tempMonth+1
     end   
     
      select @mm= count(*) from #temp
    print 'the temp table have:'+cast(@mm as varchar(100))

     set @temp1='select  msgtype_id  ,convert(varchar(10),recvtime,120) recvtime , total=count(*)     from #temp  '
     set @temp2=' group by  convert(varchar(10),recvtime,120), msgtype_id  '
     set @tempTotal = @temp1+''+@temp2
     print @tempTotal
     set @tempUndeal = @temp1+' where (iswait=1 and DealSign=0) or DealSign=2  or (iswait=1 and DealSign=8) '+@temp2
     print @tempUndeal
     set @tempdealing =@temp1+' where (iswait=1 and DealSign=1) or (iswait=1 and DealSign=9) '+@temp2
     print @tempdealing
     set @tempdealed =@temp1+' where (iswait=0 and DealSign=3) or (iswait=0 and DealSign=4) '+@temp2
     print @tempdealed

     set @temp3='select a.MsgType_ID , a.RecvTime,a.total,
     (select undeal= case  when b.total IS NULL  then 0 else b.total end) undeal ,
     (select dealing= case  when c.total IS NULL  then 0 else c.total end) dealing,
     (select dealed= case  when d.total IS NULL  then 0 else d.total end) dealed,
      undealDetail=null,dealingDetail=null,dealedDetail=null from ('+@tempTotal+') a left join ('
        +@tempUndeal+') b on a.msgtype_id=b.msgtype_id and a.recvtime=b.recvtime left join ('+
        @tempdealing+' ) c on a.msgtype_id=c.msgtype_id and a.recvtime=c.recvtime left join ( '
        +@tempdealed+') d on a.msgtype_id=d.msgtype_id and a.recvtime=d.recvtime'
    
   print '#temp1 SQL: '+ @temp3

create table #temp1  (
                     MsgType_ID int,                      
                     RecvTime varchar(10), 
                     total int,
                     undeal int,                      
                     dealing int,                     
                     dealed int,
                     undealDetail varchar(1000),
                     dealingDetail varchar(1000),
                     dealedDetail varchar(1000),                    
                     ) 

 insert into #temp1   exec(@temp3)


      select @mm1= count(*) from #temp1
print 'the #templ have :'+cast(@mm1 as varchar(100))

declare finalReport cursor 
  for select MsgType_ID,convert(varchar(10),recvtime,120) RecvTime ,dealed from #temp1
   open finalReport

     declare @MsgType_IDf int
     declare @RecvTimef varchar(10)
     declare @dealedf int
      print  'start tatal one record';
     fetch next from finalReport into @MsgType_IDf, @RecvTimef ,@dealedf
      while(@@fetch_status=0)
         begin

             declare my_cursor cursor 
              
              for  select a.msgtype_id ,a.recvtime,a.size,a.reMsgType_ID,b.ReMsgTypeName from
                     (select msgtype_id,count(*) size, convert(varchar(10),recvtime,120) recvtime,reMsgType_ID from #temp 
                       where convert(varchar(10),recvtime,120)=@RecvTimef and msgtype_id=@MsgType_IDf and (iswait=0 and (DealSign=3 or DealSign=4))   group by  convert(varchar(10),recvtime,120), msgtype_id,reMsgType_ID) a left join T_ReMsgType b
                      on a.reMsgType_ID=b.reMsgType_ID order by a.msgtype_id

                open my_cursor

                 declare @msgtype_id int
                 declare @recvtime varchar(10)
                 declare @reMsgType_ID int
                 declare @ReMsgTypeName varchar(500)
                 declare @dealedDetail varchar(500)
                 declare @size int
                 declare @flag int

                 set @flag=0
                 set @dealedDetail='总数:'+Cast(@dealedf as varchar(5))+' (其中 '
                  print  'start deal one record';
               fetch next from my_cursor into @msgtype_id,@recvtime,@size,@reMsgType_ID,@ReMsgTypeName
                 while(@@fetch_status=0)
                       begin

                         set @dealedDetail= @dealedDetail+@ReMsgTypeName+': '+                 
                         Cast((select undeal= case  when @size IS NULL  then 0  else @size end) as varchar(5))+','
                          set @flag=1  
                  
                         fetch next from my_cursor into @msgtype_id,@recvtime,@size,@reMsgType_ID,@ReMsgTypeName
                       end

                   if @flag=0
                       begin
                       set @dealedDetail=substring(@dealedDetail,0,len(@dealedDetail)-3)
                       end
                    else 
                       begin
                         set @dealedDetail=substring(@dealedDetail,0,len(@dealedDetail))+')'
                       end
                close my_cursor 
                DEALLOCATE my_cursor
          update #temp1 set dealedDetail= @dealedDetail  where convert(varchar(10),recvtime,120)=@RecvTimef and msgtype_id=@MsgType_IDf 
            print  'have update one record';

     fetch next from finalReport into @MsgType_IDf, @RecvTimef ,@dealedf
          end
    close finalReport
    DEALLOCATE finalReport

 select c.*,d.MsgTypeName from #temp1 c left join T_MsgType d on c.msgtype_id=D.msgtype_id  order by c.RecvTime,c.msgtype_id