sql server 小计汇总的实现
来源:互联网 发布:淘宝清洗订单会降权吗 编辑:程序博客网 时间:2024/05/29 11:20
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
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
0 0
- sql server 小计汇总的实现
- SQL学习(三) 轻松实现汇总小计(ROLLUP/CUBE)
- SQL分组小计功能的实现
- sql小计汇总 rollup用法
- sql小计汇总 rollup用法实例分析
- ms sql 行转列 实现表中统计与小计的
- SQL SERVER的分类汇总
- sql server的sql语句汇总
- SQL server 2005 默认值或绑定 ---- 让“小计” 列的值默认为“数量”乘“单价”
- 求SQL实现小计与累计?
- Sql使用WITH ROLLUP 进行分类汇总及小计功能
- sql小计
- sql server 优化 汇总
- SQL Server 问题汇总
- SQL Server 知识汇总
- SQL Server 版本号汇总
- SQL Server 版本号汇总
- 关于SQL中Function 递归的小计
- 用hexo和github-pages搭建博客
- unity3D 调用android .so
- android 获取当前时间
- 第26篇 jQuery 快速学习下
- 漫谈计算摄像学 (二):利用光场实现“先拍照后对焦”
- sql server 小计汇总的实现
- android 各国时区
- javaweb学习总结(五)——Servlet开发(一)
- Android Training - 创建自定义的Views(Lesson 2 - 自定义Drawing)
- Android中常用控件分类总结
- Get请求 Post请求
- 笔记十二(绘制一)
- Struts2声明式验证
- Android Training - 创建自定义的Views(Lesson 3 - 处理View的交互性)