sql server 小计汇总的实现

USE [BJYD]
GO
/****** Object:  StoredProcedure [dbo].[proc_get_degree_of_satisfy]    Script Date: 2015/12/5 星期六 上午 11:28:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--满意度综合统计
-- ================================================


ALTER PROCEDURE   [dbo].[proc_get_degree_of_satisfy]
    (
    @callTableName varchar(100),
    @center  varchar(50),
    @zu varchar(50),
    @beginTime varchar(50),
    @endTime varchar(50),
@type int 
)
AS
BEGIN
declare  @sql varchar(max);
SET NOCOUNT ON;


set @sql='


select  ' 


if(@type=0)
 
begin 


 set @sql=@sql+'


case when GROUPING(a.center)=1 then ''总计'' else a.center end ,
case when GROUPING(a.center)=0 and GROUPING(a.zu)=1 then ''小计'' else a.zu end,'


end 
else if(@type=1)


begin 


 set @sql=@sql+'


case when GROUPING(a.center)=1 then ''总计'' else a.center end ,
case when GROUPING(a.center)=1 and GROUPING(a.zu)=1 and GROUPING(uid)=1 and GROUPING(usr)=1  then ''总计'' else a.zu end,'
end  


 


if(@type=1)
 
begin 


set @sql=@sql+' a.uid,a.usr,' 


end 


set @sql=@sql+'


sum(b.接受回访数),
sum(a.转接成功数),
sum(a.客户按键总数),
sum(a.非常满意),
sum(a.满意),
sum(a.一般),
case when sum(b.接受回访数)<>0 then  ltrim(cast (sum(a.转接成功数)*100.0/(case when sum(b.接受回访数)=0 then 1 else sum(b.接受回访数) end) as decimal(18,2) ))+''%'' else '''' end,
case when sum(a.转接成功数)<>0 then ltrim(cast (sum(a.客户按键总数)*100.0/(case when sum(a.转接成功数)=0 then 1 else sum(a.转接成功数) end) as decimal(18,2)))+''%'' else '''' end,
   case when sum(a.客户按键总数)<>0 then  ltrim(cast (((sum(a.非常满意)+sum(a.满意))*100.0/(case when sum(a.客户按键总数)=0 then 1 else sum(a.客户按键总数) end ) ) as decimal(18,2)))+''%'' else '''' end,
case when sum(a.客户按键总数)<>0 then ltrim(cast ((sum(a.非常满意)*100.0/( case when sum(a.客户按键总数)=0 then 1 else sum(a.客户按键总数) end ) ) as decimal(18,2)))+''%''  else '''' end 
        --sum(a.客户按键总数),sum(a.非常满意),sum(a.满意),sum(a.一般),sum(b.接受回访数)
from 
(
    select  center,zu , '


if(@type=1)
 
begin 


set @sql=@sql+'  uid, usr,' 


end 


set @sql=@sql+'
COUNT(*) as 转接成功数,
SUM(case when Grade<>0 then 1 else 0 end) as    客户按键总数,
    SUM(case when Grade=1 then 1 else 0 end) as    非常满意,
    SUM(case when Grade=2 then 1 else 0 end) as     满意,
    SUM(case when Grade=3 then 1 else 0 end) as     一般
    from TBL_PISATISFY 
    left join usr on   ISNUMERIC(usr.uid)=1 and   usr.uid=TBL_PISATISFY.agentId   
    where ';
    
set @sql=@sql+' 
LogTime >='''+@beginTime+' 00:00:00'' and LogTime <='''+@endTime+' 23:59:59'' and ISNUMERIC(usr.uid)=1 '
    if (@center is not null and @center<>'null')
    begin
       set @sql=@sql+' and center='''+@center+'''';
    end
     if (@zu is not null and @zu<>'null')
    begin
      set @sql=@sql+' and zu='''+@zu+'''';
    end  
  set @sql=@sql+' 
  
  group by   center,zu ' 


  if(@type=1)
 
begin 


set @sql=@sql+' , uid, usr' 


end 
set @sql=@sql+' 
) a 
 
left join 


(
  select     center,zu  ,'
  
    if(@type=1)
 
begin 


set @sql=@sql+'   hfr, operid,' 


end 
  set @sql=@sql+'
  COUNT(distinct phone) as 接受回访数
  from '+@callTableName+'
  left join usr on   '+@callTableName+'.OperID=usr.uid 
  where   
i_data >= '''+@beginTime+' 00:00:00'' and    
i_data <='''+@endTime+' 23:59:59''    and jttype=''接受回访''  '

if (@center is not null and @center<>'null')
    begin
      set @sql=@sql+' and center='''+@center+'''';
    end
  if (@zu is not null and  @zu<>'null')
    begin
      set @sql=@sql+' and zu='''+@zu+'''';
    end  
    
  set @sql=@sql+' 
  
  group by   center,zu '


      if(@type=1)
 
begin 


set @sql=@sql+' , hfr, operid' 


end 
    
set @sql=@sql+'
     
 ) b
 
on a.center=b.center and a.zu=b.zu '


      if(@type=1)
 
begin 


set @sql=@sql+' and a.uid=b.operid and a.usr=b.hfr ' 


end 
    
 set @sql=@sql+'
 
group by a.center,a.zu '


      if(@type=1)
 
begin 


set @sql=@sql+'   ,a.uid,a.usr ' 


end 
    
 set @sql=@sql+'


 with rollup  '
 
       if(@type=1)
 
begin 


set @sql=@sql+'  having  (GROUPING(usr)=1 and GROUPING(a.center)=1 and GROUPING(a.zu)=1  and GROUPING(uid)=1)  or (GROUPING(usr)=0 and GROUPING(a.center)=0 and GROUPING(a.zu)=0  and GROUPING(uid)=0 )  ' 


end 
    
 set @sql=@sql+'
 
 order by 
 
CHARINDEX(a.center,''一中心二中心三中心七中心八中心淮一中心淮二中心培训组重点团队凤凰团队辣妈团队总计'')


 ,a.zu desc '


print @sql;
    
    EXEC(@sql) 
     
SET NOCOUNT OFF;
END


 --proc_get_degree_of_satisfy '[call_process_bak_2015-1]','null','null','2015-01-01','2015-01-31',1