统计问题

Question

1.时间差是秒（可以换算成分钟）
2.ccustomno(客户队列号)不会有重复，统计的时候可以不理
3.总队列数是统计所有队列
4.时间间隔固定为1（如果可以自定义设置最好^_^）

Answer 1

--试试(没数据测试)create proc p_qry
@时间间隔 int=1
as
declare @ii int,@count int,@i intselect @ii=max(datediff(second,dtarrivetime,dtoptime1))
,@count=count(*)
from d_customlist_histset @i=@count/@时间间隔+1set rowcount @i
select id=identity(int,1,1) into #t
from syscolumns a,syscolumns b
set rowcount 0select 时间段=cast(b.id*@时间间隔+1 as varchar)+'-'
+cast(b.id*@时间间隔+@时间间隔 as varchar)
,队列数,[占总队列数%]
from(
select 时间间隔=(datediff(second,dtarrivetime,dtoptime1)-1)
,队列数=count(*)
,[占总队列数%]=cast(cast(sum(100.0)/@count as decimal(20,2)) as varchar)+'%'
from  d_customlist_hist
group by (datediff(second,dtarrivetime,dtoptime1)-1)
)a join #t b on a.时间段/@时间间隔=b.id
go

Answer 2

--试试(没数据测试)create proc p_qry
@时间间隔 int=1
as
declare @ii int,@count int,@i intselect @ii=max(datediff(second,dtarrivetime,dtoptime1))
,@count=count(*)
from d_customlist_histset @i=@count/@时间间隔+1set rowcount @i
select id=identity(int,1,1) into #t
from syscolumns a,syscolumns b
set rowcount 0select 时间段=cast(b.id*@时间间隔+1 as varchar)+'-'
+cast(b.id*@时间间隔+@时间间隔 as varchar)
,队列数,[占总队列数%]
from(
select 时间间隔=(datediff(second,dtarrivetime,dtoptime1)-1)
,队列数=count(*)
,[占总队列数%]=cast(cast(sum(100.0)/@count as decimal(20,2)) as varchar)+'%'
from  d_customlist_hist
group by (datediff(second,dtarrivetime,dtoptime1)-1)
)a join #t b on a.时间间隔/@时间间隔=b.id  --是:时间间隔
go

Answer 3

2004-02-28 08:30:08.000 125 报装 NULL 1001 2004-02-28 08:35:08.000 2004-02-28 08:37:07.983 0 1
2004-02-28 16:20:25.000 9 投诉建议 NULL 1001 2004-02-28 16:28:25.000 2004-02-28 16:31:25.000 5 1
2004-02-28 16:18:25.000 52 缴费 NULL 1002 2004-02-28 16:28:25.000 2004-02-28 16:32:25.000 0 2
2004-02-29 16:35:32.000 30 业务咨询 NULL 1002 2004-02-29 16:35:53.000 2004-02-29 16:36:23.160 26 1
2004-02-29 16:38:13.000 31 业务咨询 NULL 1001 2004-02-29 16:39:50.000 2004-02-29 16:40:50.223 27 3
2004-03-08 10:56:36.000 57 话费查询 NULL 1001 2004-03-08 10:56:57.000 2004-03-08 11:11:11.000 53 1
2004-03-08 10:57:20.000 58 话费查询 NULL 1001 2004-03-08 10:57:33.000 2004-03-08 11:02:53.000 54 3
2004-03-08 10:57:37.000 59 话费查询 NULL 1001 2004-03-08 10:57:46.000 2004-03-08 11:01:46.000 55 2
2004-03-08 10:49:46.000 61 话费查询 NULL 1001 2004-03-08 10:57:46.000 2004-03-08 11:07:46.000 57 1
2004-03-08 10:50:46.000 62 故障申报 NULL 1001 2004-03-08 10:57:46.000 2004-03-08 11:03:46.000 58 3
2004-03-08 15:48:40.000 64 业务咨询 NULL 1001 2004-03-08 16:14:55.000 2004-03-08 16:18:55.000 60 1
2004-02-29 15:33:23.000 23 业务咨询 NULL 1002 2004-02-29 15:41:24.000 2004-02-29 15:46:24.000 19 2
2004-02-29 15:33:23.000 126 缴费 NULL 1002 2004-02-29 15:48:26.000 2004-02-29 15:54:26.000 0 1
2004-02-29 16:07:44.000 24 业务办理 NULL 1001 2004-02-29 16:11:53.000 2004-02-29 16:17:53.000 20 3
2004-02-29 16:12:01.000 25 业务办理 NULL 1001 2004-02-29 16:12:42.000 2004-02-29 16:19:42.000 21 2
2004-02-29 16:26:40.000 29 故障申报 NULL 1002 2004-02-29 16:34:07.000 2004-02-29 16:39:07.000 25 1
2004-02-29 16:46:50.000 34 业务咨询 NULL 1001 2004-02-29 16:47:33.000 2004-02-29 16:53:33.000 30 1
2004-02-29 16:46:54.000 35 业务办理 NULL 1001 2004-02-29 16:47:56.000 2004-02-29 16:52:56.000 31 2
2004-02-29 16:49:48.000 36 话费查询 NULL 1001 2004-02-29 16:55:26.000 2004-02-29 17:00:26.000 32 1
2004-02-29 17:09:19.000 40 业务办理 NULL 1001 2004-02-29 17:09:53.000 2004-02-29 17:19:53.000 36 3
2004-02-29 17:10:52.000 41 话费查询 NULL 1002 2004-02-29 17:11:48.000 2004-02-29 17:18:48.000 37 2
2004-02-29 17:15:38.000 42 故障申报 NULL 1002 2004-02-29 17:35:13.000 2004-02-29 17:39:13.000 38 1

Answer 4

--OK
create proc p_qry
@时间间隔 int=1
as
declare @max int,@min int,@count int,@i intselect @max=max(datediff(second,dtarrivetime,dtoptime1))
,@min=min(datediff(second,dtarrivetime,dtoptime1))
,@count=count(*)
from d_customlist_histset @i=(@max-@min)/@时间间隔+1set rowcount @i
select id=identity(int,0,1),b=cast(0 as bit) into #t from syscolumns
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t(b) select 0 from syscolumns
set @i=@i-@@rowcount
end
set rowcount 0select 时间间隔=cast(id*@时间间隔+@min as varchar)
+'-'+cast((id+1)*@时间间隔+@min as varchar)
,队列数=isnull(队列数,0)
,[占总队列数%]=isnull([占总队列数%],'')
from(
select 时间间隔=(datediff(second,dtarrivetime,dtoptime1)-@min)/@时间间隔
,队列数=count(*)
,[占总队列数%]=cast(cast(sum(100.0)/@count as decimal(20,2)) as varchar)+'%'
from  d_customlist_hist
group by (datediff(second,dtarrivetime,dtoptime1)-@min)/@时间间隔
)a right join #t b on a.时间间隔=b.id
order by id,时间间隔
go

Answer 5

--测试数据
create table d_customlist_hist(
dtarrivetime datetime,
ccustomno varchar(10),
ctype varchar(10),
copno varchar(10),
dtoptime1 datetime,
dtoptime2 datetime,
cwindowno int,a int)
insert d_customlist_hist
select '2004-02-28 08:30:08.000','125','报装','1001','2004-02-28 08:35:08.000','2004-02-28 08:37:07.983','0',1
union all select '2004-02-28 16:20:25.000','9','投诉建议','1001','2004-02-28 16:28:25.000','2004-02-28 16:31:25.000','5',1
union all select '2004-02-28 16:18:25.000','52','缴费','1002','2004-02-28 16:28:25.000','2004-02-28 16:32:25.000','0',2
union all select '2004-02-29 16:35:32.000','30','业务咨询','1002','2004-02-29 16:35:53.000','2004-02-29 16:36:23.160','26',1
union all select '2004-02-29 16:38:13.000','31','业务咨询','1001','2004-02-29 16:39:50.000','2004-02-29 16:40:50.223','27',3
union all select '2004-03-08 10:56:36.000','57','话费查询','1001','2004-03-08 10:56:57.000','2004-03-08 11:11:11.000','53',1
union all select '2004-03-08 10:57:20.000','58','话费查询','1001','2004-03-08 10:57:33.000','2004-03-08 11:02:53.000','54',3
union all select '2004-03-08 10:57:37.000','59','话费查询','1001','2004-03-08 10:57:46.000','2004-03-08 11:01:46.000','55',2
union all select '2004-03-08 10:49:46.000','61','话费查询','1001','2004-03-08 10:57:46.000','2004-03-08 11:07:46.000','57',1
union all select '2004-03-08 10:50:46.000','62','故障申报','1001','2004-03-08 10:57:46.000','2004-03-08 11:03:46.000','58',3
union all select '2004-03-08 15:48:40.000','64','业务咨询','1001','2004-03-08 16:14:55.000','2004-03-08 16:18:55.000','60',1
union all select '2004-02-29 15:33:23.000','23','业务咨询','1002','2004-02-29 15:41:24.000','2004-02-29 15:46:24.000','19',2
union all select '2004-02-29 15:33:23.000','126','缴费','1002','2004-02-29 15:48:26.000','2004-02-29 15:54:26.000','0',1
union all select '2004-02-29 16:07:44.000','24','业务办理','1001','2004-02-29 16:11:53.000','2004-02-29 16:17:53.000','20',3
union all select '2004-02-29 16:12:01.000','25','业务办理','1001','2004-02-29 16:12:42.000','2004-02-29 16:19:42.000','21',2
union all select '2004-02-29 16:26:40.000','29','故障申报','1002','2004-02-29 16:34:07.000','2004-02-29 16:39:07.000','25',1
union all select '2004-02-29 16:46:50.000','34','业务咨询','1001','2004-02-29 16:47:33.000','2004-02-29 16:53:33.000','30',1
union all select '2004-02-29 16:46:54.000','35','业务办理','1001','2004-02-29 16:47:56.000','2004-02-29 16:52:56.000','31',2
union all select '2004-02-29 16:49:48.000','36','话费查询','1001','2004-02-29 16:55:26.000','2004-02-29 17:00:26.000','32',1
union all select '2004-02-29 17:09:19.000','40','业务办理','1001','2004-02-29 17:09:53.000','2004-02-29 17:19:53.000','36',3
union all select '2004-02-29 17:10:52.000','41','话费查询','1002','2004-02-29 17:11:48.000','2004-02-29 17:18:48.000','37',2
union all select '2004-02-29 17:15:38.000','42','故障申报','1002','2004-02-29 17:35:13.000','2004-02-29 17:39:13.000','38',1
go--OK
create proc p_qry
@时间间隔 int=1
as
declare @max int,@min int,@count int,@i intselect @max=max(datediff(second,dtarrivetime,dtoptime1))
,@min=min(datediff(second,dtarrivetime,dtoptime1))
,@count=count(*)
from d_customlist_histset @i=(@max-@min)/@时间间隔+1set rowcount @i
select id=identity(int,0,1),b=cast(0 as bit) into #t from syscolumns
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t(b) select 0 from syscolumns
set @i=@i-@@rowcount
end
set rowcount 0select 时间间隔=cast(id*@时间间隔+@min as varchar)
+'-'+cast((id+1)*@时间间隔+@min as varchar)
,队列数=isnull(队列数,0)
,[占总队列数%]=isnull([占总队列数%],'')
from(
select 时间间隔=(datediff(second,dtarrivetime,dtoptime1)-@min)/@时间间隔
,队列数=count(*)
,[占总队列数%]=cast(cast(sum(100.0)/@count as decimal(20,2)) as varchar)+'%'
from  d_customlist_hist
group by (datediff(second,dtarrivetime,dtoptime1)-@min)/@时间间隔
)a right join #t b on a.时间间隔=b.id
order by id,时间间隔
go--调用
exec p_qry 300
go--删除测试环境
drop table d_customlist_hist
drop proc p_qry/*--测试结果
时间间隔              队列数      占总队列数%     
-------------------- ----------- ---------------
9-309                12          54.55%
309-609              7           31.82%
609-909              1           4.55%
909-1209             1           4.55%
1209-1509            0           
1509-1809            1           4.55%（所影响的行数为 6 行）
--*/